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

Select from multiple subclassed entities generates SQL syntax error


    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.6
    • Fix Version/s: 2.1.7
    • Component/s: DQL
    • Security Level: All
    • Labels:
    • Environment:
      Symfony 2.0.12
      PHP 5.3.10
      MySQL 5.1


      I have 2 entities subclassing a "master" entity using single table inheritance:

      createdAt datetime

      PhotoHistoryItem extends HistoryItem
      photo: (foreign key to Photo entity)

      CommentHistoryItem extends HistoryItem
      comment: (foreign key to Comment entity)

      I want to select all of them using the following DQL:

      SELECT p, c FROM ProFolioBundle:PhotoHistoryItem p, ProFolioBundle:CommentHistoryItem c LEFT JOIN p.photo LEFT JOIN c.comment

      But the generated SQL has an error in the final AND syntax (it generates "AND AND"):

      SELECT h0_.id AS id0, h0_.createdAt AS createdAt1, h1_.id AS id2, h1_.createdAt AS createdAt3, h0_.type AS type4, h1_.type AS type5 FROM HistoryItem h0_, HistoryItem h1_ LEFT JOIN Photo p2_ ON h0_.photo_id = p2_.id INNER JOIN Comment c3_ ON h1_.comment_id = c3_.id WHERE (h0_.type IN ('photo') AND AND h1_.type IN ('comment')) LIMIT 20

      The error MySQL throws is:

      SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND h1_.type IN ('comment')) LIMIT 20' at line 1

      1. Comment.php
        5 kB
        Nicolas Laplante
      2. CommentHistoryItem.php
        0.9 kB
        Nicolas Laplante
      3. HistoryItem.php
        1 kB
        Nicolas Laplante
      4. Photo.php
        10 kB
        Nicolas Laplante
      5. PhotoHistoryItem.php
        0.9 kB
        Nicolas Laplante



          • Assignee:
            beberlei Benjamin Eberlei
            nlaplante Nicolas Laplante
          • Votes:
            0 Vote for this issue
            2 Start watching this issue


            • Created: