[DDC-2235] Single table inheritance discriminator in WHERE when using arbitrary join syntax Created: 11/Jan/13  Updated: 08/Nov/14  Resolved: 16/Aug/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Jordi Forns Assignee: Guilherme Blanco
Resolution: Fixed Votes: 6
Labels: None

Issue Links:
Duplicate
duplicates DDC-1940 Doctrine DQL: erroneous sql generatio... Resolved

 Description   

The condition on the discriminator column is placed in the WHERE clause when using arbitrary join syntax, which renders LEFT JOINs useless.

Given these classes:
A - no inheritance
B1 - abstract, root of a hierarchy, discriminator column is named 'type'
I setup a query builder like this:

$qb->select('a.id AS idA, b.id AS idB')
    ->from('\Entity\A', 'a')
    ->leftJoin('\Entity\B1', 'b', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.something=b.something');
And the SQL Doctrine generates is something like this:
SELECT a.id, b.id FROM a LEFT JOIN b ON (a.something=b.something) WHERE b.type IN ('1', '2', '3')

The problems is that the WHERE condition makes the left join useless.

The condition on the discriminator column should be placed in the JOIN clause to avoid the problem.



 Comments   
Comment by Ondrej Hlavaty [ 10/Feb/13 ]

Can this be somehow worked around? If not, it is really serious problem...

Comment by Jordi Forns [ 18/Feb/13 ]

I couldn't find any workaround.
Trying to force the 'type' condition in the join clause resulted useless as Doctrine would add the 'where' condition regardless.

Comment by Michel Salib [ 22/Mar/13 ]

Easier way to workaround right now, is to declare a OneToMany from class A to class B on a protected field (no need of getter or setter). That way you can do classic join via relationship transversing and then the condition will be placed in the ON part of the query.

Comment by Yuta Konishi [ 01/Apr/13 ]

I could access with below codes. You should use RAW SQL it is easy solution I guess. good luck.

$qb = $em->createQueryBuilder();

$qb->select('a, b')
->from('YourEntity1', 'a')
->leftJoin('YourEntity2', 'b', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.id = b.relationId');

$raw_sql = $qb->where( 
	$qb->expr()->in('a.relationId', $ids)
)
->orderBy('a.updatedAt', 'DESC')
->setMaxResults(10)
->getQuery()->getSQL();

$conn = $em->getConnection();
$stmt = $conn->query($raw_sql);

/* $stmt->fetchAll(); // access as Array */
Comment by Benjamin Eberlei [ 04/Apr/13 ]

Assigned to Alexander

Comment by Benjamin Eberlei [ 14/Apr/13 ]

Duplicate of DDC-1940

Comment by Jordi Forns [ 22/Apr/13 ]

Benjamin: this bug doesn't seem to be a dupe of DDC-1940. Actually that issue doesn't seem to be a bug at all.

As a reminder, the problem in this issue is that when performing arbitrary left joins on entities that are part of a class hierarchy, the discriminator condition is placed in the where clause instead of the join clause. This means that rows that could not be joined will have null values in the discriminator column and thus will not be returned because of the where condition (which will contain something like " where x.discriminator in (1,2,3) ").

Comment by Tom Arnfeld [ 27/Apr/13 ]

Has this issue been resolved elsewhere? From reading over DDC-1940 it doesn't seem to be a duplicate at all. I'm experiencing the same problem as Jordi and can't seem to find a solution. Is there any particular reason the `IN ()` predicate is not a part of the join, but instead placed in the main `WHERE` clause?

Comment by Tom Arnfeld [ 28/Apr/13 ]

I've been looking into the root cause of this bug (or feature..) to try and understand why it's happening, and after trying various possible fixes (a little hard without full understanding of the Doctrine/Query internals) I've ended up with a fix that seems to work well for my use case, at least. I've not run any of the unit tests (I plan to, and may adjust my fix based on that) but the top revision is my change... https://gist.github.com/tarnfeld/a6bb50ec707c7af1c5dc/revisions

Would love some feedback.

Pull request here: https://github.com/doctrine/doctrine2/pull/656

Comment by Jordi Forns [ 29/Apr/13 ]

Tom's fix moves the condition of the discriminator column to the LEFT JOIN, which is exactly what was needed.

Alexander: could you please give it a look?

Comment by Jordi Forns [ 23/May/13 ]

Tom's proposal seems to fix the issue.

Comment by Gordon Forsythe [ 11/Jun/13 ]

Agree, Tom's pull request looks like it fixes the issue. Is there a reason this hasn't been merged yet?

Comment by Gordon Forsythe [ 24/Jun/13 ]

I believe this may have been fixed by #DDC-2506 but I haven't tested it yet.
There is a pull request for it https://github.com/doctrine/doctrine2/pull/708 and there has been recent activity.
Disregard, not related.

Comment by Tom Arnfeld [ 24/Jun/13 ]

The two don't seem to be at all related to me, or maybe i'm missing something? Also, this PR has been open for two months, with no sign of being merged :/

Comment by Doctrine Bot [ 13/Aug/13 ]

A related Github Pull-Request [GH-708] was closed:
https://github.com/doctrine/doctrine2/pull/708

Comment by Benjamin Eberlei [ 13/Aug/13 ]

Gordon Forsythe I don't like your tone. This is an open-source project and we do this in our free time. Please respect that we cannot offer specific response times at all.

Comment by Doctrine Bot [ 16/Aug/13 ]

A related Github Pull-Request [GH-656] was closed:
https://github.com/doctrine/doctrine2/pull/656

Comment by Marco Pivetta [ 16/Aug/13 ]

Fixed at https://github.com/doctrine/doctrine2/commit/605c32dbb384e25117625a7cb4db4e7319a16bae ( https://github.com/doctrine/doctrine2/pull/758 )

Comment by Benjamin Eberlei [ 20/Aug/13 ]

merged into 2.4

Comment by Doctrine Bot [ 09/Aug/14 ]

A related Github Pull-Request [GH-656] was closed:
https://github.com/doctrine/dbal/pull/656

Comment by Doctrine Bot [ 27/Oct/14 ]

A related Github Pull-Request [GH-708] was closed:
https://github.com/doctrine/dbal/pull/708

Comment by Doctrine Bot [ 08/Nov/14 ]

A related Github Pull-Request [GH-708] was assigned:
https://github.com/doctrine/dbal/pull/708

Generated at Thu Nov 27 18:25:47 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.