Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2634

Adding a WITH clause on leftJoin places the JOIN ... AND in the wrong place combined with inheritance mapping

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.4
    • Fix Version/s: 2.4.1
    • Component/s: DQL
    • Security Level: All
    • Environment:
      MySQL 5.6

      Description

      I implement following querybuilder:
      $qb = $this->createQueryBuilder('it')
      ->select('it, i')
      ->leftJoin('it.items', 'i', 'WITH', 'i.archived = false')
      ->orderBy('it.position', 'ASC')
      ;

      Note: the result is the same with
      return $this->getEntityManager()
      ->createQuery('
      SELECT it, i FROM AcmeDemoBundle:ItemType it
      LEFT JOIN it.items i WITH i.archived = 0
      ORDER BY it.position ASC')
      ->getResult();

      The Item entity has inheritance mapping to (at the moment) one subclass, ObjectItem. The archived property is on the Item class (not ObjectItem).

      The generated query is
      SELECT
      ...
      FROM
      item_type i0_
      LEFT JOIN item i1_ ON i0_.id = i1_.item_type_id
      LEFT JOIN object_item o2_ ON i1_.id = o2_.id
      AND (i1_.archived = 0)
      ORDER BY
      i0_.position ASC

      This causes issues and unexpected in MySQL and a wrong set of results. The generated query should be
      SELECT
      ...
      FROM
      item_type i0_
      LEFT JOIN item i1_ ON i0_.id = i1_.item_type_id
      AND (i1_.archived = 0)
      LEFT JOIN object_item o2_ ON i1_.id = o2_.id
      ORDER BY
      i0_.position ASC

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Can you check this again with 2.4? I think we have fixed this bug in 2.4

        Show
        Benjamin Eberlei added a comment - Can you check this again with 2.4? I think we have fixed this bug in 2.4
        Hide
        Bram Van der Sype added a comment -

        Saw you (Benjamin) walking around phpbnl14 and remembered this. Just reproduced it in 2.4 and works now. So fixed, can be closed.

        Show
        Bram Van der Sype added a comment - Saw you (Benjamin) walking around phpbnl14 and remembered this. Just reproduced it in 2.4 and works now. So fixed, can be closed.
        Hide
        Bram Van der Sype added a comment -

        reproduced in 2.4, works now.

        Show
        Bram Van der Sype added a comment - reproduced in 2.4, works now.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Bram Van der Sype
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: