How to use left join like right join in Doctrine

Question

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!

Answer 1

@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();

Answer 2

try this one:

SELECT
    parenttable.*, childtable.*
FROM childtable
RIGHT JOIN parenttable ON parenttable.id = childtable.parent_id
WHERE
    parenttable.parent_id IS NULL
    AND
    childtable.childid IS NOT NULL;

Answer 3

the left join you posted did not have: WHERE parent.parent_entity_id is null

i am going to guess that within your data, this means the record is itself not a child.

so if you just need the right join sql you provided as a left join, here you go:

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

NOTE: if your data allows for multiple generations, e.g. grand-children etc., the where clause above is filtering out not only children, but also grand-children. This has implications on the child data returned: children will not be returned if they are grand-children.

Answer 4

Not really familiar with Doctrine script, but... This old-style JOIN should achieve the same result as a RIGHT JOIN in most SQL scripts:

    SELECT parent.*, child.*
    FROM table child, table parent 
    WHERE parent.id = child.parent_id
    OR child.parent_id IS NULL;

Answer 5

These two statements are equivalent:

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

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

I never use RIGHT JOIN. I think it is only included in SQL for completeness.