Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: 2.4, 2.5, 2.4.1, 2.4.2
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:

      Description

      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 tbl_score_level 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

        Activity

        Hide
        Chesley Brown added a comment -

        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.

        Show
        Chesley Brown added a comment - 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.
        Hide
        Jeroen Thora added a comment -

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

        Show
        Jeroen Thora added a comment - I have added a failing test for this problem in doctrine/dbal#548

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Jeroen Thora
          • Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: