Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2497

When trying to join single-table inheritance classes in DQL or QueryBuilder, Doctrine creates incorrect SQL

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 2.3.4
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      PHP5.4, Mysql stable, ubuntu 12.04

      Description

      When writing a DQL query joining to inherited subclasses, Doctrine incorrectly injects the discriminator in the WHERE clause instead of in the JOIN, where it should be.
      Example: 2 classes extend Person via JOINED table inheritance: EmployedPerson and UnemployedPerson. I want to create a query where I pull separate class-specific properties form each class:

      SELECT p FROM Person p
      LEFT JOIN EmployedPerson ep WITH ep=p
      LEFT JOIN UnemployedPerson up WITH up=p

      The SQL from this will look something like:
      SELECT .... FROM person p
      LEFT JOIN person ep ON(ep.id=p.id)
      LEFT JOIN person up ON(up.id=p.id)
      WHERE ep.discrim IN('employed') AND up.discrim IN('unemployed')

      The problem is this query will produce no results if one of the subclasses have no results.

      The fix would be putting the discriminator in the JOIN so the sql would be:

      SELECT .... FROM person p
      LEFT JOIN person ep ON(ep.id=p.id AND ep.discrim IN('employed'))
      LEFT JOIN person up ON(up.id=p.id AND up.discrim IN('unemployed'))

      I will try to create a unit test based on the current ones and upload it.

        Activity

        Hide
        Gordon Forsythe added a comment -

        Sorry, this is a duplicate of http://www.doctrine-project.org/jira/browse/DDC-2235
        I don't see where to mark as dupe...

        Show
        Gordon Forsythe added a comment - Sorry, this is a duplicate of http://www.doctrine-project.org/jira/browse/DDC-2235 I don't see where to mark as dupe...
        Hide
        Marco Pivetta added a comment -

        Duplicate of DDC-2235

        Show
        Marco Pivetta added a comment - Duplicate of DDC-2235
        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
        Hide
        Doctrine Bot added a comment -

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

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

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Gordon Forsythe
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: