DDC-2235: Single table inheritance discriminator in WHERE when using arbitrary join syntax #2934

Closed
doctrinebot opened this Issue Jan 11, 2013 · 24 comments

2 participants

@doctrinebot

Jira issue originally created by user jay.eff:

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.

@doctrinebot

Comment created by aearsis:

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

@doctrinebot

Comment created by jay.eff:

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.

@doctrinebot

Comment created by michelsalib:

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.

@doctrinebot

Comment created by kmusiclife:

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 **/
@doctrinebot

Comment created by @beberlei:

Assigned to Alexander

@doctrinebot

Comment created by @beberlei:

Duplicate of DDC-1940

@doctrinebot

Comment created by jay.eff:

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) ").

@doctrinebot

Comment created by tarnfeld:

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?

@doctrinebot

Comment created by tarnfeld:

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: #656

@doctrinebot

Comment created by jay.eff:

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?

@doctrinebot

Comment created by jay.eff:

Tom's proposal seems to fix the issue.

@doctrinebot

Comment created by baohx2000:

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

@doctrinebot

Comment created by baohx2000:

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

@doctrinebot

Comment created by tarnfeld:

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 :/

@doctrinebot

Comment created by @doctrinebot:

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

@doctrinebot

Comment created by @beberlei:

[~baohx2000] 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.

@doctrinebot

Comment created by @doctrinebot:

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

@doctrinebot

Comment created by @ocramius:

Fixed at 605c32d ( #758 )

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by @beberlei:

merged into 2.4

@doctrinebot

Comment created by @doctrinebot:

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

@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

@doctrinebot doctrinebot added this to the 2.4 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment