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.
          Benjamin Eberlei made changes -
          Assignee Benjamin Eberlei [ beberlei ] Alexander [ asm89 ]
          Benjamin Eberlei made changes -
          Link This issue duplicates DDC-1940 [ DDC-1940 ]
          Benjamin Eberlei made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Duplicate [ 3 ]
          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? ]
          Jordi Forns made changes -
          Resolution Duplicate [ 3 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Gordon Forsythe made changes -
          Status Reopened [ 4 ] Awaiting Feedback [ 10000 ]
          Gordon Forsythe made changes -
          Comment [ Did the devs go on extended vacation? ]
          Marco Pivetta made changes -
          Assignee Alexander [ asm89 ] Marco Pivetta [ ocramius ]
          Marco Pivetta made changes -
          Status Awaiting Feedback [ 10000 ] Open [ 1 ]
          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 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved: