Sql – How to use left join like right join in Doctrine

doctrineleft-joinsql

So I've been racking my brain for a while now. In Doctrine there is no concept of right join. I know that you can use a left join like a right join but I can't figure it out for my example which is why I'm here.

My example:
I have an entity in Doctrine which has a one-to-one relationship with itself called "parent".
I am trying to get all entities and their children(if they exist) with no duplicates.

With a right join this is simple because I can say: 

    SELECT parent.*, child.*
    FROM table child
    RIGHT JOIN table parent ON parent.id = child.parent_id
    WHERE parent.parent_id is null;

But using a left join I am returned results that I can not figure out what the where clause should be to filter them out.

So my question is "Does doctrine have a way of doing right join" or "How can I use the left join operation like a right join"?

—EDIT—

You guys are correct in what you are saying about changing the ordering of tables I select from but I am using Doctrine so the relationship does child->parent. Here is my doctrine query:

My Entity:

/**
 * @Entity
 * @Table(name="entity")
 */
class Entity
{
...
/**
 * @OneToOne(
 *     targetEntity="Entity",
 *     fetch="EAGER"
 * )
 * @JoinColumn(name="parent_id", referencedColumnName="id")
 */
private $parent;
...
}

My doctrine select statement:

$em->createQueryBuilder()
->select(array('child', 'parent'))
->from('Entity', 'child')
->leftjoin('child.parent', 'parent')
->orderBy('parent.id','asc')
->getQuery()
->execute();

I am not sure how and IF I can switch the ordering of tables. I also tried creating another relationship from entity to itself (like the way I did parent) but called it child. But when I updated the db with the new schema doctrine threw errors.

Any ideas? And Thanks for the quick responses!

—EDIT 2—

Left join sql and results:

SELECT child.id, child.changed_timestamp, child.parent_entity_id, parent.id,
  parent.changed_timestamp, parent.parent_entity_id 
FROM content child 
LEFT JOIN content parent ON child.parent_entity_id = parent.id   
ORDER BY parent.id ASC

child_id    child_timestamp parent_entity_id    parent_id   parent_timestamp    parent_entity_id
1           8/16/12 20:29   NULL                NULL        NULL                NULL
7           9/20/12 16:07   NULL                NULL        NULL                NULL
8           8/17/12 16:08   NULL                NULL        NULL                NULL
9           8/17/12 20:44   NULL                NULL        NULL                NULL
10          8/17/12 21:03   NULL                NULL        NULL                NULL
11          8/17/12 21:17   NULL                NULL        NULL                NULL
194         9/19/12 9:58    NULL                NULL        NULL                NULL
195         9/20/12 10:38   NULL                NULL        NULL                NULL
196         9/19/12 11:58   NULL                NULL        NULL                NULL
197         NULL            196                 196         9/19/12 11:58       NULL
200         9/20/12 16:02   1                   1           8/16/12 20:29       NULL
202         9/20/12 16:35   NULL                NULL        NULL                NULL
204         9/21/12 8:41    NULL                NULL        NULL                NULL
206         NULL            204                 204         9/21/12 8:41        NULL

Right join results:

SELECT child.id, child.changed_timestamp, child.parent_entity_id, parent.id, 
 parent.changed_timestamp, parent.parent_entity_id 
FROM content child 
RIGHT JOIN content parent ON child.parent_entity_id = parent.id   
WHERE parent.parent_entity_id is null
ORDER BY parent.id ASC

child_id    child_timestamp parent_entity_id    parent_id   parent_timestamp    parent_entity_id
200         9/20/12 16:02   1                   1           8/16/12 20:29       NULL
NULL        NULL            NULL                7           9/20/12 16:07       NULL
NULL        NULL            NULL                8           8/17/12 16:08       NULL
NULL        NULL            NULL                9           8/17/12 20:44       NULL
NULL        NULL            NULL                10          8/17/12 21:03       NULL
NULL        NULL            NULL                11          8/17/12 21:17       NULL
NULL        NULL            NULL                194         9/19/12 9:58        NULL
NULL        NULL            NULL                195         9/20/12 10:38       NULL
197         NULL            196                 196         9/19/12 11:58       NULL
NULL        NULL            NULL                202         9/20/12 16:35       NULL
206         NULL            204                 204         9/21/12 8:41        NULL

I want to achieve the results with the right join sql. Unique parent entities with their associated children(if they exists) but I need to achieve it using doctrine. Thanks again!

Best Answer

@491243's comment is correct: if you were using sql this would be a trivial problem because left and right joins are easily convertible and relational predicates can be expressed in any order.

The real problem appears not to be how to rewrite a right join as a left one but how to traverse your relationship in the other direction. Presumably you can't just do something like this?: 

$em->createQueryBuilder()
->select(array('child', 'parent'))
->from('Entity', 'parent')
->leftjoin('parent.child', 'child')
->orderBy('parent.id','asc')
->getQuery()
->execute();
Related Topic