Uploaded image for project: 'Doctrine 2 - ORM'
  1. Doctrine 2 - ORM
  2. DDC-349

Add support for specifying precedence in joins in DQL


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


      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


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


            • Assignee:
              romanb Roman S. Borschel
              dennis.verspuij Dennis Verspuij
            • Votes:
              1 Vote for this issue
              1 Start watching this issue


              • Created: