Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1435

Exception thrown when generating SQL from a DQL subselect where the entity has a foreign key as a primary key

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: Git Master
    • Fix Version/s: 2.1.3
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

      If an entity has a foreign key as part of its primary key and you try to query that entity in a subselect clause in DQL, an exception is thrown in ClassMetadataInfo->getQuotedColumnName. The problem seems to be that it's only considering the field mappings rather than both the field and association mappings when it's looking for the correct column name.

      Example entities:

      • Article(id primary key, title, content)
      • ArticleTag(article_id, tag, primary key (article_id, tag))

      Example DQL: select art from Article art where exists (select tag from ArticleTag tag where tag.article = art)

        Activity

        Hide
        Benjamin Eberlei added a comment - - edited

        Verified:

            /**
             * @group DDC-1435
             */
            public function testForeignKeyAsPrimaryKeySubselect()
            {
                $this->assertSqlGeneration(
                    "SELECT s FROM Doctrine\Tests\Models\DDC117\DDC117Article s WHERE EXISTS (SELECT r FROM Doctrine\Tests\Models\DDC117\DDC117Reference r WHERE r.source = s)",
                    ""
                );
            }
        

        This however is not possible anyways, since you cannot have "r" in a SimpleSelectExpression when it resolves to multiple columns. You can try to select just one arbitrary field, for example."SELECT r.foobar FROM .."

        Show
        Benjamin Eberlei added a comment - - edited Verified: /** * @group DDC-1435 */ public function testForeignKeyAsPrimaryKeySubselect() { $ this ->assertSqlGeneration( "SELECT s FROM Doctrine\Tests\Models\DDC117\DDC117Article s WHERE EXISTS (SELECT r FROM Doctrine\Tests\Models\DDC117\DDC117Reference r WHERE r.source = s)" , "" ); } This however is not possible anyways, since you cannot have "r" in a SimpleSelectExpression when it resolves to multiple columns. You can try to select just one arbitrary field, for example."SELECT r.foobar FROM .."
        Hide
        Payam Hekmat added a comment -

        Would that just be a documentation issue then? I didn't see that restriction in the grammar, but I may have overlooked it. I issued a pull request for this issue assuming that an association mapped field is valid input for getQuotedColumnName.

        Selecting a single field does work fine.

        Show
        Payam Hekmat added a comment - Would that just be a documentation issue then? I didn't see that restriction in the grammar , but I may have overlooked it. I issued a pull request for this issue assuming that an association mapped field is valid input for getQuotedColumnName. Selecting a single field does work fine.
        Hide
        Benjamin Eberlei added a comment -

        Did you touch this in your latest changes to SimpleSelectExpression guilherme?

        Show
        Benjamin Eberlei added a comment - Did you touch this in your latest changes to SimpleSelectExpression guilherme?
        Hide
        Guilherme Blanco added a comment -

        Hi,

        @beberlei No. Issue was totally unrelated.
        Please merge my commit to 2.1.X.

        The issue was caused by the identifier iteration was considering that @Id fields are always fields, never associations. You may see that in method getQuoteColumnName.

        Solution was to create a new method called getQuotedIdenitiferColumnNames and consume it in SqlWalker.
        This addresses the issue.

        Committed patch and test case as of: https://github.com/doctrine/doctrine2/commit/0ec2cc557f51d6240396689e36101f62d84d2a38

        Cheers,

        Show
        Guilherme Blanco added a comment - Hi, @beberlei No. Issue was totally unrelated. Please merge my commit to 2.1.X. The issue was caused by the identifier iteration was considering that @Id fields are always fields, never associations. You may see that in method getQuoteColumnName. Solution was to create a new method called getQuotedIdenitiferColumnNames and consume it in SqlWalker. This addresses the issue. Committed patch and test case as of: https://github.com/doctrine/doctrine2/commit/0ec2cc557f51d6240396689e36101f62d84d2a38 Cheers,
        Hide
        Benjamin Eberlei added a comment -

        Merged into 2.1.x

        Show
        Benjamin Eberlei added a comment - Merged into 2.1.x
        Hide
        Benjamin Eberlei added a comment -

        This issue is referenced in Github Pull-Request GH-159
        https://github.com/doctrine/doctrine2/pull/159

        Show
        Benjamin Eberlei added a comment - This issue is referenced in Github Pull-Request GH-159 https://github.com/doctrine/doctrine2/pull/159
        Hide
        Benjamin Eberlei added a comment -

        A related Github Pull-Request [GH-159] was opened
        https://github.com/doctrine/dbal/pull/159

        Show
        Benjamin Eberlei added a comment - A related Github Pull-Request [GH-159] was opened https://github.com/doctrine/dbal/pull/159

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Payam Hekmat
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: