doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2026-01-22 14:04:24 +01:00 by admin · 24 comments
admin commented 2026-01-22 14:04:24 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Jan 11, 2013).

Originally assigned to: @guilhermeblanco on GitHub.

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.

Originally created by @doctrinebot on GitHub (Jan 11, 2013). Originally assigned to: @guilhermeblanco on GitHub. 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.
admin added the Bug label 2026-01-22 14:04:24 +01:00
admin closed this issue 2026-01-22 14:04:25 +01:00
admin commented 2026-01-22 14:04:25 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jan 11, 2013):

@doctrinebot commented on GitHub (Jan 11, 2013): - duplicates [DDC-1940: Doctrine DQL: erroneous sql generation from dql join with "WITH" or "WHERE" clause](http://www.doctrine-project.org/jira/browse/DDC-1940)
admin commented 2026-01-22 14:04:26 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Feb 10, 2013):

Comment created by aearsis:

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

@doctrinebot commented on GitHub (Feb 10, 2013): Comment created by aearsis: Can this be somehow worked around? If not, it is really serious problem...
admin commented 2026-01-22 14:04:27 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Feb 18, 2013):

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 commented on GitHub (Feb 18, 2013): 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.
admin commented 2026-01-22 14:04:27 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Mar 22, 2013):

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 commented on GitHub (Mar 22, 2013): 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.
admin commented 2026-01-22 14:04:28 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 1, 2013):

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 commented on GitHub (Apr 1, 2013): Comment created by kmusiclife: I could access with below codes. You should use RAW SQL it is easy solution I guess. good luck. ``` java $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 **/ ```
admin commented 2026-01-22 14:04:28 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 4, 2013):

Comment created by @beberlei:

Assigned to Alexander

@doctrinebot commented on GitHub (Apr 4, 2013): Comment created by @beberlei: Assigned to Alexander
admin commented 2026-01-22 14:04:29 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 14, 2013):

Comment created by @beberlei:

Duplicate of DDC-1940

@doctrinebot commented on GitHub (Apr 14, 2013): Comment created by @beberlei: Duplicate of [DDC-1940](http://www.doctrine-project.org/jira/browse/DDC-1940)
admin commented 2026-01-22 14:04:29 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 22, 2013):

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 commented on GitHub (Apr 22, 2013): Comment created by jay.eff: Benjamin: this bug doesn't seem to be a dupe of [DDC-1940](http://www.doctrine-project.org/jira/browse/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) ").
admin commented 2026-01-22 14:04:29 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 27, 2013):

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 commented on GitHub (Apr 27, 2013): Comment created by tarnfeld: Has this issue been resolved elsewhere? From reading over [DDC-1940](http://www.doctrine-project.org/jira/browse/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?
admin commented 2026-01-22 14:04:29 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 28, 2013):

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: https://github.com/doctrine/doctrine2/pull/656

@doctrinebot commented on GitHub (Apr 28, 2013): 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: https://github.com/doctrine/doctrine2/pull/656
admin commented 2026-01-22 14:04:29 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 29, 2013):

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 commented on GitHub (Apr 29, 2013): 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?
admin commented 2026-01-22 14:04:30 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 23, 2013):

Comment created by jay.eff:

Tom's proposal seems to fix the issue.

@doctrinebot commented on GitHub (May 23, 2013): Comment created by jay.eff: Tom's proposal seems to fix the issue.
admin commented 2026-01-22 14:04:30 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jun 11, 2013):

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 commented on GitHub (Jun 11, 2013): 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?
admin commented 2026-01-22 14:04:30 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jun 24, 2013):

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 https://github.com/doctrine/doctrine2/pull/708 and there has been recent activity.
Disregard, not related.

@doctrinebot commented on GitHub (Jun 24, 2013): Comment created by baohx2000: I believe this may have been fixed by #[DDC-2506](http://www.doctrine-project.org/jira/browse/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.
admin commented 2026-01-22 14:04:31 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jun 24, 2013):

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 commented on GitHub (Jun 24, 2013): 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 :/
admin commented 2026-01-22 14:04:31 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 13, 2013):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Aug 13, 2013): Comment created by @doctrinebot: A related Github Pull-Request [GH-708] was closed: https://github.com/doctrine/doctrine2/pull/708
admin commented 2026-01-22 14:04:31 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 13, 2013):

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 commented on GitHub (Aug 13, 2013): 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.
admin commented 2026-01-22 14:04:32 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 16, 2013):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Aug 16, 2013): Comment created by @doctrinebot: A related Github Pull-Request [GH-656] was closed: https://github.com/doctrine/doctrine2/pull/656
admin commented 2026-01-22 14:04:32 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 16, 2013):

Comment created by @ocramius:

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

@doctrinebot commented on GitHub (Aug 16, 2013): Comment created by @ocramius: Fixed at https://github.com/doctrine/doctrine2/commit/605c32dbb384e25117625a7cb4db4e7319a16bae ( https://github.com/doctrine/doctrine2/pull/758 )
admin commented 2026-01-22 14:04:32 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 16, 2013):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Aug 16, 2013): Issue was closed with resolution "Fixed"
admin commented 2026-01-22 14:04:32 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 20, 2013):

Comment created by @beberlei:

merged into 2.4

@doctrinebot commented on GitHub (Aug 20, 2013): Comment created by @beberlei: merged into 2.4
admin commented 2026-01-22 14:04:32 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 9, 2014):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Aug 9, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-656] was closed: https://github.com/doctrine/dbal/pull/656
admin commented 2026-01-22 14:04:33 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Oct 27, 2014):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Oct 27, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-708] was closed: https://github.com/doctrine/dbal/pull/708
admin commented 2026-01-22 14:04:34 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 8, 2014):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Nov 8, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-708] was assigned: https://github.com/doctrine/dbal/pull/708
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2809
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?