Details
-
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: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
- is duplicated by
-
DDC-1256
Generated SQL error with DQL WITH and JOINED inheritance
-
Activity
Ok, I have not given up yet... 
, here's a "stupid" example.
Imagine a book store that sells books of various authors and keeps track of those sales.
Let's say you would have an admin page that lists all authors, and for each author
its also shows the books and their sales dates since january 1st, but only for those
books that were actually sold and contain an A in its name. An optimized SQL query
to fetch all the information at once would be something like:
SELECT A., B., S.*
FROM author A
LEFT JOIN (
book B
INNER JOIN sale S ON S.book_id = B.id AND S.dt >= '2010-01-01'
) ON B.author_id = A.id AND A.name LIKE '%A%'
In DQL it would then be something like:
SELECT A., B., S.*
FROM author A
LEFT JOIN (
book B
INNER JOIN sale S WITH S.dt >= '2010-01-01'
) WITH A.name LIKE '%A%'
If the database would contain thousands of books, but sales for just a
few books, this will definitely perform better than using subselects.
Off course one would like to fetch array graphs instead of objects for
further optimization, but this hopefully shows my point.
I have attached a test casefor a similar query, though without the additional
join constraints for clarity. I surely hope you can consider it.
One last note, you shouldn't be afraid that nesting joins is not in the
ansi SQL spec. Select queries are about record sets and products
between these sets, tables are just the basic means of providing record
sets to the query. This is an important terminological difference to think about.
Specifying precedence with parenthesis around joins is a logical and
natural evolution of the ansi sql standard. For example views are a good
proof of this concept, I could define book B INNER JOIN sale S as a view
and LEFT JOIN that to authors to get effectively the same result
set as the above example. The database server would internally perform the
same query (though may additionally take indexes on the view into account).
That said, rdbm's that support this syntax would certainly never drop the
feature, as its not a feature but just plain logical and smart querying!
P.S. I had a hard time finding out how to run the test cases, I could not find
it in the Doctrine 2 documentation, development wiki, cookbook or any other
place, while finally it was as easy as running phpunit Doctrine_Tests_AllTests
from within the tests/ directory, or just phpunit Doctrine_Tests_ORM_Functional_Ticket_DDC349Test
for my test. Could you please add some info about this somewhere, it might
save others some googling.
This seems to be a valid issue to me.
This implementation is the actual solution to associations retrieval that are inherited (type joined).
Example:
/** Joined */ class Base {} class Foo extends Base {} class Bar { public $foo; } // This causes the CTI to link as INNER JOIN, which makes the result become 0 // il if you have no Foo's defined (although it should ignore this) $q = $this->_em->createQuery('SELECT b, f FROM Bar b LEFT JOIN b.foo f');