Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1225

Invalid SQL generated (extra comma) when joining to entity with composite PK

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.1
    • Fix Version/s: 2.1.2, 2.2
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

                  $qb->from('Tournaments_Model_StageBracketTeamRegistration', 'r')
                     ->innerJoin('r.teamSelection', 'ts')
                     ->innerJoin('ts.players', 'tsp')
                     ->select('r, ts, tsp')
                     ->where('r.stageBracket = ?1')
                     ->andWhere('r.opponentIsReserve = false')
                     ->orderBy('r.registrationDateTime')
                     ->setParameter(1, $bracket);

      Generates:

      SELECT s0_.id AS id0, s0_.opponent_is_reserve AS opponent_is_reserve1, s0_.opponent_checked_in AS opponent_checked_in2, s0_.registration_date_time AS registration_date_time3, t1_.id AS id4,, s0_.type AS type5, s0_.stage_bracket_id AS stage_bracket_id6, s2_.team_selection_id AS team_selection_id7, t1_.team_id AS team_id8, t3_.team_selection_id AS team_selection_id9, t3_.player_id AS player_id10
      FROM stage_bracket_team_registrations s2_
      INNER JOIN stage_bracket_registrations s0_ ON s2_.id = s0_.id
      INNER JOIN team_selections t1_ ON s2_.team_selection_id = t1_.id
      INNER JOIN team_selection_players t3_ ON t1_.id = t3_.team_selection_id
      WHERE s0_.stage_bracket_id = 22 AND s0_.opponent_is_reserve = 0
      ORDER BY s0_.registration_date_time ASC

      Note the 2nd comma after "t1_.id AS id4". TeamSelectionPlayer uses a composite PK. I have attached the relevant entity classes.

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Merged into 2.1.x

        Show
        Benjamin Eberlei added a comment - Merged into 2.1.x
        Hide
        Guilherme Blanco added a comment -
        Show
        Guilherme Blanco added a comment - Fixed in this commit https://github.com/doctrine/doctrine2/commit/6857134f36097187ab2f0d932f4f1d9ffab12854 Thanks for the report!
        Hide
        Glen Ainscow added a comment -

        Hi Guilherme,

        I'll attach 2 simple entities for testing. You can run the following query:

                $qb->from('App_Model_TestEntity1', 'te1')
                   ->select('te1')
                   ->where('te1.testEntity2 = ?1')
                   ->setParameter(1, 0)
                   ->getQuery()
                   ->getOneOrNullResult();
        

        I'm running this against 2.1.0 + this change.

        Thanks.

        Show
        Glen Ainscow added a comment - Hi Guilherme, I'll attach 2 simple entities for testing. You can run the following query: $qb->from('App_Model_TestEntity1', 'te1') ->select('te1') ->where('te1.testEntity2 = ?1') ->setParameter(1, 0) ->getQuery() ->getOneOrNullResult(); I'm running this against 2.1.0 + this change. Thanks.
        Hide
        Guilherme Blanco added a comment -

        Hi,

        I attempted to create a failing test case for this issue, but either the provided entities are not enough or the issue is not reproducible anymore (it was already fixed in latest 2.2-DEV).

        Could you please try to compile everything into a test case?

        Cheers,

        Show
        Guilherme Blanco added a comment - Hi, I attempted to create a failing test case for this issue, but either the provided entities are not enough or the issue is not reproducible anymore (it was already fixed in latest 2.2-DEV). Could you please try to compile everything into a test case? Cheers,
        Hide
        Glen Ainscow added a comment -

        This is still an issue:

                    $qb->from('Tournaments_Model_StageBracketRegisteredPlayer', 'p')
                       ->select('p')
                       ->where('p.stageBracket = ?1')
                       ->andWhere('p.player = ?2')
                       ->setParameter(1, $bracket)
                       ->setParameter(2, $player)
                       ->getQuery()
                       ->getOneOrNullResult();
        

        Results in:

        SELECT , s0_.stage_bracket_id AS stage_bracket_id0, s0_.player_id AS player_id1, s0_.game_account_id AS game_account_id2 FROM stage_bracket_registered_players s0_ WHERE s0_.stage_bracket_id = 14 AND s0_.player_id = 5
        
        Show
        Glen Ainscow added a comment - This is still an issue: $qb->from('Tournaments_Model_StageBracketRegisteredPlayer', 'p') ->select('p') ->where('p.stageBracket = ?1') ->andWhere('p.player = ?2') ->setParameter(1, $bracket) ->setParameter(2, $player) ->getQuery() ->getOneOrNullResult(); Results in: SELECT , s0_.stage_bracket_id AS stage_bracket_id0, s0_.player_id AS player_id1, s0_.game_account_id AS game_account_id2 FROM stage_bracket_registered_players s0_ WHERE s0_.stage_bracket_id = 14 AND s0_.player_id = 5
        Hide
        Glen Ainscow added a comment -
        Show
        Glen Ainscow added a comment - No worries, found the changes here: https://github.com/doctrine/doctrine2/commit/196632978cf39bc3914e14739767cb5b72a8df9d
        Hide
        Glen Ainscow added a comment -

        Where can I find the changeset? Would it be easy for me to apply the changes to 2.1.0?

        Show
        Glen Ainscow added a comment - Where can I find the changeset? Would it be easy for me to apply the changes to 2.1.0?
        Hide
        Benjamin Eberlei added a comment -

        Fixed

        Show
        Benjamin Eberlei added a comment - Fixed

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Glen Ainscow
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: