How to use left join like right join in Doctrine

Question

In Doctrine there is no right join. You can use a left join like a right join but I can't figure it out for 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 cannot figure out how to filter with the where clause.

How can I use a left join to get a right join in Doctrine?

I can change the ordering of tables but I am using Doctrine so the relationship does child->parent.

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 the entity to itself (like I did parent) but called it "child". But when I updated the database with the new schema Doctrine threw errors.

SQL left join 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

SQL right join and 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

`child RIGHT JOIN tableparent ON parent.id = child.parent_id` is the same as `tableparent LEFT JOIN child ON parent.id = child.parent_id` — John Woo, Sep 21 '12 at 15:09

score 0 · Answer 1 · edited Jan 28 '23 at 14:32

If you were using SQL this would be trivial because left and right joins are easily convertible and relational predicates can be expressed in any order.

The 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.

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

score 0 · Answer 2 · edited Jan 28 '23 at 14:28

0

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;

edited Jan 28 '23 at 14:28

philipxy

14,867
6
39
83

answered Dec 17 '13 at 20:38

user3112708

46
2

2

Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post – chrslg Jan 29 '23 at 00:21

score 0 · Answer 3 · edited Jan 28 '23 at 14:31

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

Within your data, this means the record is itself not a child.

So if you just need your SQL right join as a left join:

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

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.

score 0 · Answer 4 · edited Jan 28 '23 at 14:27

0

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;

edited Jan 28 '23 at 14:27

philipxy

14,867
6
39
83

answered Jun 28 '14 at 07:13

Phrancis

2,222
2
27
40

score 0 · Answer 5 · answered Dec 04 '17 at 12:29

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.

How to use left join like right join in Doctrine

5 Answers5