DDC-3410: Allow Query Builder to specify the joins of Join Inheritance entities

[doctrinebot]

Jira issue originally created by user dave.newson:

Possibly a duplicate of DDC-16 in essence; resolving this would probably resolve that issue.

Summary
When you SELECT an entity which is a superclass using Joined Inheritance, Doctrine automatically adds it's own JOINs of the superclass or subclass tables.
These automatic JOINs that are introduced can't be used in DQL/builder, so you can't do anything with them (further joins on their associations for eager loading, WHERE queries, etc).
Allow me to specify my own Joins between the superclass and subclass so I can perform further queries deeper in the associations.

Main culprit:
https://github.com/doctrine/doctrine2/blob/2.4/lib/Doctrine/ORM/Query/SqlWalker.php#L341

Long version
Superclass "Activity" uses the Joined inheritance type with a discriminator column. There are various activities such as ActivityDocument, ActivityTask, etc.
These sub-class activities have associations to other entities; ActivityDocument associates to a Document entity, and Document then associates to User.

For the query, I want to fetch all Activity where the Activities document/task is associated to a given user. If I wanted to do this in raw SQL it would look something like this:

SELECT a.**, ad.*, d.*, at.*, t.**
FROM Activity a
LEFT JOIN ActivityDocument ad ON ad.id = a.id
LEFT JOIN Document d ON d.id = ad.document_id
LEFT JOIN ActivityTask at ON at.id = a.id
LEFT JOIN Task t ON t.id = at.task_id
WHERE
t.user*id = 1 OR d.user*id = 1

Doctrine supports the joined inheritance type, so I want it to fetch the subclasses and also eagerly load the downstream Document or Task entity that's associated with them, and be able to execute clauses on the data.

I can only get half way there:

$builder
    ->select('a')
    ->from('Activity', 'a')
    ->leftJoin('ActivityDocument', 'ad', Query\Expr\Join::WITH, 'a.id = ad.id')
    ->leftJoin('ad.document', 'ad_d')
    ->orWhere('ad_d.user = :user')
    ->leftJoin('ActivityTask', 'at', Query\Expr\Join::WITH, 'a.id = at.id')
    ->leftJoin('at.task', 'at_t')
    ->orWhere('at_t.user = :user')
    ->setParameter('user', $user);

In the above I've fudged the joined inheritance association using ->leftJoin() in order to execute the deep WHERE portion, however because we're doing our own join between two entities, the association between Activity and ActivityDocument is lost.

The entities returned by this query are instances of ActivityDocument and ActivityTask; the hydrated subclasses are returned when we SELECT from Activity, and ActivityDocument and ActivityTask are LEFT JOINed automatically by Doctrine because the Activity entity is recognised as using Joined Inheritance, but there's no way to latch onto these generated LEFT JOINs.
https://github.com/doctrine/doctrine2/blob/2.4/lib/Doctrine/ORM/Query/SqlWalker.php#L376

Note that adding "ad", "at" or "at_d" or "at_t" to the select does not solve this.

A workaround for this specific scenario is this:
bq. $builder->select('ad', 'at', 'ad_d', 'at_t')
This fetch ActivityDocument and ActivityTask specifically, plus their associations. Unfortunately because it is across multiple to-level entities, it causes null rows to be returned in the result set.

Effectively this goes the other way, and adds joins from ActivityDocument to Activity in order to provide the correct hydration of ActivityDocument.
https://github.com/doctrine/doctrine2/blob/2.4/lib/Doctrine/ORM/Query/SqlWalker.php#L348

Note that you still cannot use an alias for Activity because the builder was not the one to specify that relation.

What I want to be able to do
Allow me to define the join across the Join Inheritance entities, so I can use the alias for the superclass/subclass in the query builder!

Rather than DDC-16's idea of "casting" to a class, just let me define the join myself. Obviously this is more a "joined class" than a "joined field" , so extra notation may be required.
A painfully ugly example of this syntax could be something like:
bq. $builder->join('Activity->ActivityDocument', 'ad')
This could then establish the Join as some form of JoinAssociation rather than a RangeVariableDeclaration.

The bigger problem is that SqlWalker::_generateClassTableInheritanceJoins doesn't have scope over any of the joins in the query, and thus can't inspect any relations that have been established in the query builder, and use those instead of generating its own.

Unfortunately I don't know the internals of Doctrine to be of any more use.

Why
Joined Inheritance is a powerful feature, but without being able to use associations across the superclass and subclass it actually creates an annoying dead-end in queries.

There is no good workaround for this issue. If you fetch a Join Inheritance entity you can only examine one side of the inheritance without performing another query.

[doctrinebot]

• is referenced by DDC-3652: Problem with joins between entities without associations

[TheCelavi]

Query (Superclass is BaseContact, children are Person and Company):

    $qb
        ->select('q')
        ->from(BaseContact::class, 'q')
        ->leftJoin(Person::class, 'p', Join::WITH, 'q = p')
        ->leftJoin(Company::class, 'c', Join::WITH, 'q = c')

Generated SQL query is (FROM part):

 FROM 
           roc_ab_base_contact r0_ 
           LEFT JOIN roc_ab_person r1_ ON r0_.id = r1_.id 
           LEFT JOIN roc_ab_company r2_ ON r0_.id = r2_.id 
           LEFT JOIN roc_ab_person r3_ 
           INNER JOIN roc_ab_base_contact r4_ ON r3_.id = r4_.id ON (r0_.id = r4_.id) 
           LEFT JOIN roc_ab_company r5_ 
           INNER JOIN roc_ab_base_contact r6_ ON r5_.id = r6_.id ON (r0_.id = r6_.id) 

You can see double joins with same tables.

Maybe to allow setting hint to a Query object not to eagerly load subclasses?

[Ocramius]

When selecting a root class, you always select all child classes: there is no such thing as selecting only the root of the inheritance...

This particular issue request seems to be over-optimization, and may be worked around via native SQL in my opinion.

[TheCelavi]

Hi Marco,

Of course that it can be done via native SQL, however, maybe if I can explain you the issue with more details, so you can feel my pain:

• I am developer, which means that I am lazy
• I am using symfony2admingenerator/AdminGenerator bundle to boost my productivity (and to work as less as possible, of course)

So, I have to implement filter by first/last name when it comes to person and first/last/company name when it comes to company (list must contain all contacts - regardless of their subtypes).

If join aliases of treeWalker are exposed/known, it can be simply done by adding "where" into query object -> a few lines of code, just to override one method of admingenerator.

However, because join aliases are not exposed, and adding joins for subclasses manually produces bad SQL query, those few lines of code can not be used, and because of that, this simple task requires a lot of coding. It can be worked around with DQL by adding something like "WHERE x IN (SELECT FROM)" -> but that is just unreasonable overhead for DB.

Hope you and Doctrine team understand the issue, and benefit of convenience having those aliases exposed/configurable.

If D2 team is willing to allow this feature, but have no resources to implement it, I would gladly help.

Thanks for your time.

[Ocramius]

@TheCelavi that's exactly the problem, actually:

If D2 team is willing to allow this feature, but have no resources to implement it, I would gladly help.

We won't be able to do that, and the issue is easily worked around by building a filter query that is SQL-specific instead.

[TheCelavi]

@Ocramius If I understood correctly, D2 team would allow this feature, but there are no resources to implement it, and since it can be worked around, issue is of a low priority?

[Ocramius]

@TheCelavi I'd probably be more inclined to veto on such a feature, because it involves substantial additional complexity, and "building it" is only 10% of the effort (maintenance would be an ongoing effort)

[fabianoroberto]

Did someone fixed this issue?

[TheLevti]

Ran into this issue today. Would be nice if that gets fixed. I have a table with many columns that I now need to write a native query for instead of using a simple query builder / DQL query.

[Ocramius]

Closing as won't fix: this is an optimisation concern that should NOT land in core.

Assumptions about which entity will be fetched in a JTI query are up to the user, and therefore should be handled in native SQL if the user of the ORM feels confident to do so.