Skip to content

Loading…

DBAL-774: DBAL parses joins in wrong order #2003

Closed
doctrinebot opened this Issue · 6 comments

2 participants

@doctrinebot

Jira issue originally created by user jeroenthora:

I have a problem that doctrine dbal orders the joins in a wrong order if you use more complex join combinations (worked fine in DBAL 2.3)

Dbal Querybuilder:

        $qb->select('tbl*profile_additional*property.pkid AS pkid')
        ->from('tbl*profile_additional_property', 'tbl_profile_additional*property')
        ->leftjoin('tbl*profile_additional_property', 'tbl_rating_system', 'tbl_rating_system', 'tbl_profile_additional_property.fk_rs = tbl_rating*system.pkid')
        ->leftjoin('tbl*rating_system', 'tbl_rating_system_translation', 'tbl_rating_system_translation', 'tbl_rating_system_translation.fk_rs = tbl_rating_system.pkid AND tbl_rating_system_translation.fk*language = :languageid')
        ->leftjoin('tbl*profile_additional_property', 'tbl_score_level', 'tbl_score_level', 'tbl_profile_additional_property.fk_scoregoal = tbl_score*level.pkid')
        ->leftjoin('tbl*rating_system_translation', 'tbl_score_level_translation', 'tbl_score_level_translation', 'tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score*level.pkid')
        ->where('tbl*profile_additional_property.fk*function = :functionid')
        ->setParameter('functionid', $functionId)
        ->setParameter('languageid', $languageId);

Expected Query:

SELECT 
tbl*profile_additional*property.pkid AS pkid 
FROM tbl*profile_additional_property tbl_profile_additional*property 
LEFT JOIN tbl*rating_system tbl_rating_system ON tbl_profile_additional_property.fk_rs = tbl_rating*system.pkid 
LEFT JOIN tbl*rating_system_translation tbl_rating_system_translation ON tbl_rating_system_translation.fk_rs = tbl_rating_system.pkid AND tbl_rating_system_translation.fk*language = :languageid 
LEFT JOIN tbl*score_level tbl_score_level ON tbl_profile_additional_property.fk_scoregoal = tbl_score*level.pkid 
LEFT JOIN tbl*score_level_translation tbl_score_level_translation ON tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score*level.pkid 
WHERE tbl*profile_additional_property.fk*function = :functionid

Resulted Query:

SELECT 
tbl*profile_additional*property.pkid AS pkid 
FROM tbl*profile_additional_property tbl_profile_additional*property 
LEFT JOIN tbl*rating_system tbl_rating_system ON tbl_profile_additional_property.fk_rs = tbl_rating*system.pkid 
LEFT JOIN tbl*rating_system_translation tbl_rating_system_translation ON tbl_rating_system_translation.fk_rs = tbl_rating_system.pkid AND tbl_rating_system_translation.fk*language = :languageid 
LEFT JOIN tbl*score_level_translation tbl_score_level_translation ON tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score*level.pkid 
LEFT JOIN tbl*score_level tbl_score_level ON tbl_profile_additional_property.fk_scoregoal = tbl_score*level.pkid 
WHERE tbl*profile_additional_property.fk*function = :functionid

(The last 2 LEFT JOINS of the query are the problem)

The problem is with getSQLForJoins it loops over all the joins and foreach join it follows all the used joins aliases until the deepest point. Therefor it will parse this join first

->leftjoin('tbl*rating_system_translation', 'tbl_score_level_translation', 'tbl_score_level_translation', 'tbl_score_level_translation.fk_rs_translation = tbl_rating_system_translation.pkid AND tbl_score_level_translation.fk_sl = tbl_score*level.pkid')

Before it generates the sql for this line (this line is needed becaus the line above needs a join on tblscorelevel first)

->leftjoin('tbl*profile_additional_property', 'tbl_score_level', 'tbl_score_level', 'tbl_profile_additional_property.fk_scoregoal = tbl_score*level.pkid')

The order the querybuilder in php is build (select, from, joins, etc) is the order it should be parsed as sql.

Ps. I have added 2.5 also as affectsversion because the code didn't change as far is i know

@doctrinebot

Comment created by chesleybrown:

Noticing this issue with v2.4 as well. However, I'm also noticing the leftJoins being ordered incorrectly on v2.3.3 as well... however the ordering between the two versions are not the same. They are both just ordered differently than the order that I actually call the leftJoin methods in.

@doctrinebot

Comment created by jeroenthora:

I have added a failing test for this problem in doctrine/dbal#548

@doctrinebot

Comment created by @doctrinebot:

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

@doctrinebot

Comment created by @ocramius:

Resolved in DBAL-991 - won't be backported in 2.4 as 2.4 behavior could radically change otherwise.

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label
@Ocramius Ocramius was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.5 milestone
@doctrinebot doctrinebot closed this
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.