Uploaded image for project: 'Doctrine 2 - ORM'
  1. Doctrine 2 - ORM
  2. DDC-2235

Single table inheritance discriminator in WHERE when using arbitrary join syntax

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: 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

          jay.eff Jordi Forns created issue -
          beberlei 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.
          beberlei Benjamin Eberlei made changes -
          Assignee Benjamin Eberlei [ beberlei ] Alexander [ asm89 ]
          beberlei Benjamin Eberlei made changes -
          Link This issue duplicates DDC-1940 [ DDC-1940 ]
          beberlei Benjamin Eberlei made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Duplicate [ 3 ]
          jay.eff 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? ]
          jay.eff Jordi Forns made changes -
          Resolution Duplicate [ 3 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          baohx2000 Gordon Forsythe made changes -
          Status Reopened [ 4 ] Awaiting Feedback [ 10000 ]
          baohx2000 Gordon Forsythe made changes -
          Comment [ Did the devs go on extended vacation? ]
          ocramius Marco Pivetta made changes -
          Assignee Alexander [ asm89 ] Marco Pivetta [ ocramius ]
          ocramius Marco Pivetta made changes -
          Status Awaiting Feedback [ 10000 ] Open [ 1 ]
          ocramius Marco Pivetta made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Assignee Marco Pivetta [ ocramius ] Guilherme Blanco [ guilhermeblanco ]
          Resolution Fixed [ 1 ]
          ocramius Marco Pivetta made changes -
          Fix Version/s 2.4 [ 10321 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved: