Doctrine 2 - ORM

Single table inheritance discriminator in WHERE when using arbitrary join syntax

Details

  • Type: Bug Bug
  • Status: Reopened Reopened
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 2.3
  • Fix Version/s: None
  • Component/s: ORM
  • Security Level: All
  • Labels:
    None

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.

Issue Links

duplicates

Bug - A problem which impairs or prevents the functions of the product. DDC-1940 Doctrine DQL: erroneous sql generation from dql join with "WITH" or "WHERE" clause

  • Major - Major loss of function.
  • Open - The issue is open and ready for the assignee to start work on it.

Activity

Ascending order - Click to sort in descending order
Benjamin Eberlei made changes -
Field Original Value New Value
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. 		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:

{code}
$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')
{code}

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.
Hide
Permalink
Ondrej Hlavaty added a comment -

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

Show
Ondrej Hlavaty added a comment - Can this be somehow worked around? If not, it is really serious problem...
Hide
Permalink
Jordi Forns added a comment -

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.

Show
Jordi Forns added a comment - 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.
Hide
Permalink
Michel Salib added a comment -

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.

Show
Michel Salib added a comment - 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.
Hide
Permalink
Yuta Konishi added a comment -

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 */
Show
Yuta Konishi added a comment - 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 */
Hide
Permalink
Benjamin Eberlei added a comment -

Assigned to Alexander

Show
Benjamin Eberlei added a comment - Assigned to Alexander
Benjamin Eberlei made changes -
Assignee Benjamin Eberlei [ beberlei ] Alexander [ asm89 ]
Benjamin Eberlei made changes -
Link This issue duplicates DDC-1940 [ DDC-1940 ]
Hide
Permalink
Benjamin Eberlei added a comment -

Duplicate of DDC-1940

Show
Benjamin Eberlei added a comment - Duplicate of DDC-1940
Benjamin Eberlei made changes -
Status Open [ 1 ] Resolved [ 5 ]
Resolution Duplicate [ 3 ]
Hide
Permalink
Jordi Forns added a comment -

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

Show
Jordi Forns added a comment - 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) ").
Hide
Permalink
Tom Arnfeld added a comment -

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?

Show
Tom Arnfeld added a comment - 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?
Hide
Permalink
Tom Arnfeld added a comment - - edited

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

Show
Tom Arnfeld added a comment - - edited 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
Jordi Forns made changes -
Comment [ Tom's fix consists in leaving the condition on the discriminator column in the WHERE clause, but allowing NULL values, which Doctrine seems to handle correctly when retrieving the result either as an array (by leaving fields null) or entities (which will be set to null).

Alexander: could you please give it a look? ]
Hide
Permalink
Jordi Forns added a comment -

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?

Show
Jordi Forns added a comment - 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?
Hide
Permalink
Jordi Forns added a comment -

Tom's proposal seems to fix the issue.

Show
Jordi Forns added a comment - Tom's proposal seems to fix the issue.
Jordi Forns made changes -
Resolution Duplicate [ 3 ]
Status Resolved [ 5 ] Reopened [ 4 ]
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-2235, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Alexander
    Reporter:
    Jordi Forns
Vote (3)
Watch (8)

Dates

  • Created:
    Updated: