Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2235

Single table inheritance discriminator in WHERE when using arbitrary join syntax

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3
    • Fix Version/s: 2.4
    • 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

          Activity

          Jordi Forns created issue -
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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 ]
          Hide
          Gordon Forsythe added a comment -

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

          Show
          Gordon Forsythe added a comment - Agree, Tom's pull request looks like it fixes the issue. Is there a reason this hasn't been merged yet?
          Hide
          Gordon Forsythe added a comment - - edited

          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.

          Show
          Gordon Forsythe added a comment - - edited 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.
          Hide
          Tom Arnfeld added a comment - - edited

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

          Show
          Tom Arnfeld added a comment - - edited 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 :/
          Gordon Forsythe made changes -
          Status Reopened [ 4 ] Awaiting Feedback [ 10000 ]
          Hide
          Doctrine Bot added a comment -

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

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-708] was closed: https://github.com/doctrine/doctrine2/pull/708
          Hide
          Benjamin Eberlei added a comment -

          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.

          Show
          Benjamin Eberlei added a comment - 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.
          Gordon Forsythe made changes -
          Comment [ Did the devs go on extended vacation? ]
          Hide
          Doctrine Bot added a comment -

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

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-656] was closed: https://github.com/doctrine/doctrine2/pull/656
          Marco Pivetta made changes -
          Assignee Alexander [ asm89 ] Marco Pivetta [ ocramius ]
          Marco Pivetta made changes -
          Status Awaiting Feedback [ 10000 ] Open [ 1 ]
          Show
          Marco Pivetta added a comment - Fixed at https://github.com/doctrine/doctrine2/commit/605c32dbb384e25117625a7cb4db4e7319a16bae ( https://github.com/doctrine/doctrine2/pull/758 )
          Marco Pivetta made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Assignee Marco Pivetta [ ocramius ] Guilherme Blanco [ guilhermeblanco ]
          Resolution Fixed [ 1 ]
          Marco Pivetta made changes -
          Fix Version/s 2.4 [ 10321 ]
          Hide
          Benjamin Eberlei added a comment -

          merged into 2.4

          Show
          Benjamin Eberlei added a comment - merged into 2.4
          Hide
          Doctrine Bot added a comment -

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

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-656] was closed: https://github.com/doctrine/dbal/pull/656

          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[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

            People

            • Assignee:
              Guilherme Blanco
              Reporter:
              Jordi Forns
            • Votes:
              6 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: