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

        Bram Van der Sype created issue -
        Bram Van der Sype made changes -
        Field Original Value New Value
        Description I implement following querybuilder:
        $qb = $this->createQueryBuilder('it')
            ->select('it, i')
            ->leftJoin('it.items', 'i', 'WITH', 'i.archived = false')
            ->orderBy('it.position', 'ASC')
        ;

        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
        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
        Bram Van der Sype made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.4.1 [ 10528 ]
        Resolution Fixed [ 1 ]

          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: