[DDC-1256] Generated SQL error with DQL WITH and JOINED inheritance Created: 06/Jul/11  Updated: 17/Feb/14  Resolved: 01/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.0.3, 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Justin Hendrickson Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 1
Labels: None
Environment:

Ubuntu 11.04


Issue Links:
Duplicate
duplicates DDC-349 Add support for specifying precedence... Open
is duplicated by DDC-2131 Fetch join not working on class table... Resolved

 Description   

I've created an entity that has a one to one relationship to a class in an inheritance tree and I'm using class table inheritance in Doctrine. When I try to add a DQL WITH statement on a column in the super class to the join, the generated SQL incorrectly places the statement in the child classes JOIN ON section.

Here's the DQL:

SELECT p FROM Fampus_Entity_Photo p LEFT JOIN p.flag f WITH f.status='ok'

Here's the generated SQL:

SELECT p0_.id AS id0, p0_.name AS name1, p0_.path AS path2, p0_.type AS type3, p0_.reference_id AS reference_id4, p0_.view_count AS view_count5, p0_.created AS created6, p0_.modified AS modified7, p0_.event_id AS event_id8, p0_.user_id AS user_id9, p0_.school_id AS school_id10, p0_.flag_id AS flag_id11 FROM photos p0_ LEFT JOIN flaggedcontent_photo f1_ ON p0_.flag_id = f1_.id AND (f2_.status = 'ok') LEFT JOIN flaggedcontent f2_ ON f1_.id = f2_.id

Note that f2_.status = 'ok' is the correct statement, but it is in the wrong LEFT JOIN ON section.

Here are my entities (significantly clipped):

/**
 * @Table(name="photos")
*/
class Photo {
    /**
     * @OneToOne(targetEntity="FlaggedContent_Photo", mappedBy="photo")
     * @JoinColumn(nullable=true)
     */
    protected $flag;
}

/**
 * @DiscriminatorColumn(name="type")
 * @DiscriminatorMap({
 *     "photo" = "FlaggedContent_Photo"
 * })
 * @InheritanceType("JOINED")
 * @Table(name="flaggedcontent")
*/
abstract class FlaggedContent {
    /**
     * Database identifier
     *
     * @Id
     * @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * Status
     *
     * @Column(type="text")
     * @var string
     */
    protected $status;
}

/**
 * @Entity
 * @Table(name="flaggedcontent_photo")
 */
class FlaggedContent_Photo extends FlaggedContent {
    /**
     * @OneToOne(targetEntity="Photo", inversedBy="flag")
     */
    protected $photo;
}


 Comments   
Comment by Michael Ridgway [ 06/Jul/11 ]

I created a test for this: https://github.com/mridgway/doctrine2/commit/1bb26a46188f180270d723e395ee707443ebdda1

I'll see if I can figure this out.

Comment by Michael Ridgway [ 11/Jul/11 ]

It doesn't seem that you would get the correct result if the WITH statement was on the 'flaggedcontent' table anyway. In order for this to work, the query would have to join 'flaggedcontent' first and then join to 'flaggedcontent_photo' based on f2_.id. While we can probably (but not easily) fix where the condition is placed, I don't think this will ever be able to give the expected results for ALL cases.

Comment by Benjamin Eberlei [ 12/Jul/11 ]

Formatting

Comment by Benjamin Eberlei [ 12/Jul/11 ]

This issue is unfixable, we tend towards throwing an exception in this case to notify developers that they cannot do this.

The only way to make it work is to move the status check to the WHERE clause.

Comment by Guilherme Blanco [ 14/Aug/11 ]

This issue is fixable by applying what we call as nested joins when generating the SQL.

This issue report #DDC-349 (http://www.doctrine-project.org/jira/browse/DDC-349) suggest us to support it in DQL, but I think we can introduce this generation on SQL only.
It's not a trivial implementation, but it would solve a couple of other issue I was looking into.

Cheers,

Comment by Benjamin Eberlei [ 01/May/13 ]

Duplicate of DDC-349

Comment by Artur Eshenbrener [ 22/Dec/13 ]

I do not understand why this issue is resolved. It is still reproduces!
Look to the source code:
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Query/SqlWalker.php#L989

// Handle WITH clause
        if ($condExpr !== null) {
            // Phase 2 AST optimization: Skip processing of ConditionalExpression
            // if only one ConditionalTerm is defined
            $sql .= ' AND (' . $this->walkConditionalExpression($condExpr) . ')';
        }

        // FIXME: these should either be nested or all forced to be left joins (DDC-XXX)
        if ($targetClass->isInheritanceTypeJoined()) {
            $sql .= $this->_generateClassTableInheritanceJoins($targetClass, $joinedDqlAlias);
        }

As you can see, sql conditions added only to first join. And after that, class table inheritance joins are added. (As in this ticket summary)
I think the best fix is adding ON conditions to last join of class table inheritance, because only in last join all needed tables are joined. In condition user can access to all tables.

Comment by Artur Eshenbrener [ 23/Dec/13 ]

https://github.com/doctrine/doctrine2/pull/886

Comment by Doctrine Bot [ 17/Feb/14 ]

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

Generated at Sat Jul 26 13:23:17 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.