DDC-2131: Fetch join not working on class table inheritance

[doctrinebot]

Jira issue originally created by user alsar:

I have an entity Appointment, that is associated with application forms.
I have an abstract class AbstractApplicationForm that has multiple (6) child classes.

The mapping info for the abstract application form looks like this:

/****
 * @ORM\Entity()
 * @ORM\Table("applicationform")
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="discr", type="string")
 * @ORM\DiscriminatorMap({
 *     "slc" = "SlcApplicationForm",
 *     "vsdb" = "VsdbApplicationForm",
 *     "vss" = "VssApplicationForm",
 *     "opzd" = "OpzdApplicationForm",
 *     "vzu" = "VzuApplicationForm",
 *     "opzdvzu" = "OpzdVzuApplicationForm"
 * })
 */
abstract class AbstractApplicationForm
...

I have an OneToMany connection from Appointment to AbstractApplicationForm.
When i construct an DQL, to get an appointment with only specific application forms it gets wrong translated into SQL.

For instance if the query builder looks like this:

$qb->select('ap, af')
   ->from(Appointment::getClass(), 'ap')
   ->leftJoin('ap.applicationForms', 'af', 'WITH', 'af.id = 123456789')
   ->andWhere('ap.id = :appointment')
   ->setParameter('appointment', $appointment);

So with this dql i should get an appointment with filtered application forms. In this case it whould return an appointment with only one application form (that with the id 123456789).
But it returns all associated application form instead on only that with the id 123456789, because it ignores the with clause.

Here is the SQL that gets generated from the DQL:

SELECT ...
FROM program*execution_activity_appointment p8_, program_execution_activity_appointment p0*
LEFT JOIN applicationform a1* ON p0_.id = a1_.appointment*id 
LEFT JOIN applicationform*slc a2_ ON a1_.id = a2*.id 
LEFT JOIN applicationform*vsdb a3_ ON a1_.id = a3*.id 
LEFT JOIN applicationform*vss a4_ ON a1_.id = a4*.id 
LEFT JOIN applicationform*opzd a5_ ON a1_.id = a5*.id 
LEFT JOIN applicationform*vzu a6_ ON a1_.id = a6*.id 
LEFT JOIN applicationform*opzdvzu a7_ ON a1_.id = a7_.id AND (a1*.id = 123456789) 
WHERE p0_.id = 1

The problem i see here is that the AND is added to the last join (applicationform_opzdvzu). But it should added to the first join (applicationform). Because the first join represents the parent entity (AbstractApplicationForm).
There is also a problem in the FROM clause. The problem is that program_execution_activity_appointment p8_ is never used anywhere else, except in the from clause.

The generated query should look like this:

SELECT ...
FROM program*execution_activity_appointment p0*
LEFT JOIN applicationform a1* ON p0_.id = a1_.appointment_id AND (a1*.id = 123456789) 
LEFT JOIN applicationform*slc a2_ ON a1_.id = a2*.id
LEFT JOIN applicationform*vsdb a3_ ON a1_.id = a3*.id
LEFT JOIN applicationform*vss a4_ ON a1_.id = a4*.id
LEFT JOIN applicationform*opzd a5_ ON a1_.id = a5*.id
LEFT JOIN applicationform*vzu a6_ ON a1_.id = a6*.id
LEFT JOIN applicationform*opzdvzu a7_ ON a1_.id = a7*.id
WHERE p0_.id = 1

This sql works as expected.

I hope i was clear enough what the problem is.

[doctrinebot]

• duplicates DDC-1256: Generated SQL error with DQL WITH and JOINED inheritance
• is referenced by DDC-2869: [GH-886] [DDC-1256] Fix applying ON/WITH conditions to first join in Class Table Inheritance

[doctrinebot]

Comment created by @beberlei:

This is a duplicate of DDC-1256

[doctrinebot]

Issue was closed with resolution "Duplicate"

[doctrinebot]

Comment created by @beberlei:

This is not easy to fix as you can see DDC-1256 is open for a while. However in your case there is an easy solution, why not fetch the appointment form by id, and then go the association to the appointment? This is the inverse operation on that relation, but suits your needs perfectly.

[doctrinebot]

Comment created by gseric:

Benjamin, this issue is duplicate of duplicate (according to your status changes). So original issues is DDC-349, but this error was introduced in 2.3. DDC-349 was created way back in 2010 so it can't be the same problem.

As far as I can tell, the problem was introduced with "Arbitrary join support" feature:
#368 (SqlWalker.php changes)
Basically, in version 2.3 and later WITH statement is wrongly handled after SqlWalker::_generateClassTableInheritanceJoins()
Before 2.3 it was handled before.

You can see it clearly in this issue's description:
should be (< 2.3):
LEFT JOIN ON AND
but we get (>= 2.3):
LEFT JOIN ON AND

This issue can't be easily avoided in situation where you want to SELECT only one child row per parent row (based on some condition). WITH is natural and fastest option.

[doctrinebot]

Comment created by gseric:

This bug seems to be recognized and fixed (pull request ATM) in DDC-2506

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-708] was closed:
#708

[doctrinebot]

Comment created by strate:

Why do you think that applying ON/WITH conditions only to base table is good? What if I will reference to fields from child entity in ON/WITH clause?
I think that all user conditions should be applied to last join in class table inheritance joins, because only in that join references to all tables are available. Otherwise I get sql error.

[doctrinebot]

Comment created by strate:

#886

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-886] was closed:
#886

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-708] was closed:
doctrine/dbal#708

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-708] was assigned:
doctrine/dbal#708