[DDC-2634] Adding a WITH clause on leftJoin places the JOIN ... AND in the wrong place combined with inheritance mapping Created: 27/Aug/13  Updated: 25/Jan/14  Resolved: 25/Jan/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.3.4
Fix Version/s: 2.4.1
Security Level: All

Type: Bug Priority: Major
Reporter: Bram Van der Sype Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: dql, inheritance, leftjoin, orm
Environment:

MySQL 5.6



 Description   

I implement following querybuilder:
$qb = $this->createQueryBuilder('it')
->select('it, i')
->leftJoin('it.items', 'i', 'WITH', 'i.archived = false')
->orderBy('it.position', 'ASC')
;

Note: the result is the same with
return $this->getEntityManager()
->createQuery('
SELECT it, i FROM AcmeDemoBundle:ItemType it
LEFT JOIN it.items i WITH i.archived = 0
ORDER BY it.position ASC')
->getResult();

The Item entity has inheritance mapping to (at the moment) one subclass, ObjectItem. The archived property is on the Item class (not ObjectItem).

The generated query is
SELECT
...
FROM
item_type i0_
LEFT JOIN item i1_ ON i0_.id = i1_.item_type_id
LEFT JOIN object_item o2_ ON i1_.id = o2_.id
AND (i1_.archived = 0)
ORDER BY
i0_.position ASC

This causes issues and unexpected in MySQL and a wrong set of results. The generated query should be
SELECT
...
FROM
item_type i0_
LEFT JOIN item i1_ ON i0_.id = i1_.item_type_id
AND (i1_.archived = 0)
LEFT JOIN object_item o2_ ON i1_.id = o2_.id
ORDER BY
i0_.position ASC



 Comments   
Comment by Benjamin Eberlei [ 08/Sep/13 ]

Can you check this again with 2.4? I think we have fixed this bug in 2.4

Comment by Bram Van der Sype [ 25/Jan/14 ]

Saw you (Benjamin) walking around phpbnl14 and remembered this. Just reproduced it in 2.4 and works now. So fixed, can be closed.

Comment by Bram Van der Sype [ 25/Jan/14 ]

reproduced in 2.4, works now.

Generated at Thu Dec 18 04:05:13 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.