Doctrine 2 - ORM

Select from multiple subclassed entities generates SQL syntax error

Details

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

Description

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

HistoryItem:
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. File
    Comment.php
    17/Apr/12 3:06 AM
    5 kB
    Nicolas Laplante
  2. File
    CommentHistoryItem.php
    17/Apr/12 3:03 AM
    0.9 kB
    Nicolas Laplante
  3. File
    HistoryItem.php
    17/Apr/12 3:03 AM
    1 kB
    Nicolas Laplante
  4. File
    Photo.php
    17/Apr/12 3:06 AM
    10 kB
    Nicolas Laplante
  5. File
    PhotoHistoryItem.php
    17/Apr/12 3:03 AM
    0.9 kB
    Nicolas Laplante

Activity

Ascending order - Click to sort in descending order
Nicolas Laplante made changes -
Field Original Value New Value
Description I have 2 entities subclassing a "master" entity using single table inheritance:

HistoryEvent:
     createdAt datetime

PhotoHistoryEvent extends HistoryEvent
    photo: (foreign key to Photo entity)

CommentHistoryEvent extends HistoryEvent
    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 		I have 2 entities subclassing a "master" entity using single table inheritance:

HistoryEvent:
     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
Hide
Permalink
Nicolas Laplante added a comment -

Fix entity names

Show
Nicolas Laplante added a comment - Fix entity names
Nicolas Laplante made changes -
Description I have 2 entities subclassing a "master" entity using single table inheritance:

HistoryEvent:
     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 		I have 2 entities subclassing a "master" entity using single table inheritance:

HistoryItem:
     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
Hide
Permalink
Fabio B. Silva added a comment -

Hi Nicolas

Could you attach your entities please ?

Show
Fabio B. Silva added a comment - Hi Nicolas Could you attach your entities please ?
Hide
Permalink
Nicolas Laplante added a comment -

Sure, here are the 3 entities

Show
Nicolas Laplante added a comment - Sure, here are the 3 entities
Nicolas Laplante made changes -
Attachment HistoryItem.php [ 11179 ]
Attachment CommentHistoryItem.php [ 11180 ]
Attachment PhotoHistoryItem.php [ 11181 ]
Hide
Permalink
Nicolas Laplante added a comment -

Here are the referenced entities in case you need them too

Show
Nicolas Laplante added a comment - Here are the referenced entities in case you need them too
Nicolas Laplante made changes -
Attachment Photo.php [ 11182 ]
Attachment Comment.php [ 11183 ]
Hide
Permalink
Benjamin Eberlei added a comment -

Fixed

Show
Benjamin Eberlei added a comment - Fixed
Benjamin Eberlei made changes -
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.1.7 [ 10198 ]
Resolution Fixed [ 1 ]
Benjamin Eberlei made changes -
Workflow jira [ 13616 ] jira-feedback [ 15316 ]
Benjamin Eberlei made changes -
Workflow jira-feedback [ 15316 ] jira-feedback2 [ 17180 ]
Benjamin Eberlei made changes -
Workflow jira-feedback2 [ 17180 ] jira-feedback3 [ 19434 ]
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1770, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Nicolas Laplante
Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: