Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-349

Add support for specifying precedence in joins in DQL

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.0-ALPHA4
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None

      Description

      This request is in followup to my doctrine-user message "Doctrine 2.0: Nested joins'.
      I am a bit surprised by the responses in that defining precedences in joins by placing parenthesis around join expressions is not well-known. Although not in the original SQL92 specification it is a major and important feature offered by all the RDBMS's that Doctrine 2 supports, and oftenly performs better than using subselects or alike. Doctrine 1 did not support it, but imho Doctrine 2 should support it to be a mature allround ORM.

      As a short example the following is a SQL statement with a nested join, where the nesting is absolutely necessary to return only a's together with either both b's and c's or no b's and c's at all:

      SELECT *
      FROM a A
      LEFT JOIN (
      b B
      INNER JOIN c C ON C.b_id = B.id
      ) ON B.a_id = A.id

      In order for Doctrine 2 to support this the BNF should be something like:
      Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" ( "(" JoinAssociationPathExpression ["AS"] AliasIdentificationVariable Join ")" | JoinAssociationPathExpression ["AS"] AliasIdentificationVariable ) [("ON" | "WITH") ConditionalExpression]
      instead of the current:
      Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" JoinAssociationPathExpression ["AS"] AliasIdentificationVariable [("ON" | "WITH") ConditionalExpression]

      This would allow DQL like:

      SELECT A, B, C
      FROM a A
      LEFT JOIN (
      A.b B
      INNER JOIN B.c C
      ) WITH B.something = 'value' AND C.something = 'othervalue'

      What further needs to be done is that the DQL parser loosly couples the ConditionalExpression to any of the previously parsed JoinAssociationPathExpression's instead of tieing it explicitely to the JoinAssociationPathExpression that preceedes it according to the old BNF notation. The new BNF should however not require any changes to the hydrator. Therefore I have the feeling that improving the DQL parser for nested joins does not require extensive work, while the benefit of running these kind of queries is considerable.

      As an extra substantiation here are links to (BNF) FROM clause documentations of the RDBMS's that Doctrine 2 supports, they all show support for nested joins:
      MySQL: http://dev.mysql.com/doc/refman/5.0/en/join.html
      PostgreSQL: http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-FROM and http://www.postgresql.org/docs/8.1/interactive/explicit-joins.html
      MSSQL: http://msdn.microsoft.com/en-us/library/ms177634.aspx
      Oracle: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#CHDDCHGF
      SQLite: http://www.sqlite.org/syntaxdiagrams.html#single-source

      I surely hope you will consider implementing this improvement because it would save me and others from the hassle of writing raw SQL queries or executing multiple (thus slow) queries in DQL for doing the same. Thanks anyway for the great product so far!

        Issue Links

          Activity

          Dennis Verspuij created issue -
          Guilherme Blanco made changes -
          Field Original Value New Value
          Link This issue duplicates DDC-512 [ DDC-512 ]
          Roman S. Borschel made changes -
          Link This issue duplicates DDC-512 [ DDC-512 ]
          Dennis Verspuij made changes -
          Attachment DDC349Test.patch [ 10569 ]
          Roman S. Borschel made changes -
          Fix Version/s 2.0 [ 10021 ]
          Benjamin Eberlei made changes -
          Workflow jira [ 10915 ] jira-feedback [ 13842 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback [ 13842 ] jira-feedback2 [ 15706 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback2 [ 15706 ] jira-feedback3 [ 17963 ]
          Benjamin Eberlei made changes -
          Link This issue is duplicated by DDC-1256 [ DDC-1256 ]

            People

            • Assignee:
              Roman S. Borschel
              Reporter:
              Dennis Verspuij
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: