Skip to content

Loading…

DDC-1256: Generated SQL error with DQL WITH and JOINED inheritance #1866

Closed
doctrinebot opened this Issue · 11 comments

2 participants

@doctrinebot

Jira issue originally created by user toxygene:

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;
}
@doctrinebot

Comment created by mridgway:

I created a test for this: mridgway@1bb26a4

I'll see if I can figure this out.

@doctrinebot

Comment created by mridgway:

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 'flaggedcontentphoto' 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.

@doctrinebot

Comment created by @beberlei:

Formatting

@doctrinebot

Comment created by @beberlei:

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.

@doctrinebot

Comment created by @guilhermeblanco:

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](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,

@doctrinebot

Comment created by @beberlei:

Duplicate of DDC-349

@doctrinebot

Issue was closed with resolution "Duplicate"

@doctrinebot

Comment created by strate:

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.

@doctrinebot

Comment created by strate:

#886

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-886] was closed:
#886

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.