|
History
| Log In
|
|
|
|
A note that ANSI join notation is available starting with 10.0.0 for OpenBase, but not before. It's also unclear if there's outer join support for OpenBase yet, but "INNER JOIN and ON" is supported.
Scott Keith <scott@openbase.com> wrote:
> We have already modified the server this week to fully > support the ANSI SQL keywords "INNER JOIN" and "LEFT OUTER JOIN". Scott verified that LEFT OUTER JOIN is also working and supported as of OpenBase 10.0.0. Mike, do we still want to support join semantics in Expressions considering EJBQLQuery already supports them?
If we're going to continue supporting Expressions, I would think that would be a good idea. However, I don't know what the future of the original Cayenne expression language/query classes will be.
I've been using the above patch for a long time now, and couldn't move to something that didn't support outer joins. Here's a rework of the patch for Cayenne 2.0.
Unfortunately, it's also including some of my other modifications like hooks for audit logging and support for raw sql trinary CustomExpressions. I'll try to pull these bits out into a separate patch when I next get a chance, but what's here passes all unit tests (except for 3 CustomExpression tests that never passed in the first place). Also, due to the following errors, I couldn't create a patch for the generated files: ========================== diff --old E:/workspaces/cayenne/STABLE-2.0/cayenne-java Inconsistent line ending style svn: File 'E:\workspaces\cayenne\STABLE-2.0\cayenne-java\src\cayenne\java\org\apache\cayenne\wocompat\parser\ParserTokenManager.java' has inconsistent newlines svn: File 'E:\workspaces\cayenne\STABLE-2.0\cayenne-java\src\cayenne\java\org\apache\cayenne\exp\parser\ExpressionParserTokenManager.java' has inconsistent newlines ========================== So you'll need to run the following cayenne-ant module ant tasks to regenerate them manually. ant -f build.xml javacc ant -f build.xml jjtree Per
* Expression.fromString() support for OUTER JOINS * Split joins (I am still unclear on the logic here... we can't automatically guess where to split the joins, and where to combine them, some explicit API for that).. Maybe we should close this Jira and handle the two tasks above as separate new Jiras? Excellent!
Split joins were explicitly indicated using "+" in the path after a relationship segment. (I had thought it was "|", but reviewing the patch seems to indicate "+"). See the SelectTranslatorOuterJoinTst .java file from the 2.0 patch file for examples like the following: + public void testCreateSqlStringForPaintingTitleAndGroupArrayNameWithOuterJoinOracle() throws Exception { + // query with joint prefetches and other joins + Expression expression = Expression.fromString( + "paintingArray+.paintingTitle <> 'a' and groupArray+.name = 'b'"); + SelectQuery q = new SelectQuery(Artist.class, expression); + + Template test = new Template() { + + void test(SelectTranslator transl) throws Exception { + + String sqlString = transl.createSqlString(); + + assertEquals( + "SELECT DISTINCT " + + + "t0.ARTIST_NAME, t0.DATE_OF_BIRTH, t0.ARTIST_ID " + + + "FROM " + + + "ARTIST t0 " + + "LEFT OUTER JOIN PAINTING t1 " + + "ON t0.ARTIST_ID = t1.ARTIST_ID " + + "LEFT OUTER JOIN ARTIST_GROUP t2 " + + "ON t0.ARTIST_ID = t2.ARTIST_ID " + + "LEFT OUTER JOIN ARTGROUP t3 " + + "ON t2.GROUP_ID = t3.GROUP_ID " + + + "WHERE " + + + "((t1.PAINTING_TITLE IS NULL) OR (t1.PAINTING_TITLE <> ?)) AND (t3.NAME = ?)", + sqlString); + } + }; + + test.testOracle(q); + } Hmm... I am also using "+" for the OUTER joins.. Split stuff is whether we should generate one or multiple joins for multiple expressions that go over the same path... I thought that is separate and should be using some other notation?
Hmm. You might be right. I honestly don't remember, but I think the two were closely related for any practical outer join query. Maybe I did use | for splits, and + for outer joins.
> but I think the two were closely related for any practical outer join query.
Logically splits and outer joins are not related. The importance of splits depends on a use case (in fact the use case that motivated me to expedite this feature didn't rely on splits). But I agree that splits are very important and this is a natural next step for us to do. > Maybe I did use | for splits, and + for outer joins. Cool. I think that's what it is. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
New Feature
Closed
Major
Add support for outer joins
Note that outer joins are best described using a "+" character after a non-mandatory relationship path component in an expression string.
Currently the parser support for such strings is poorly implemented. It was done by allowing "+" as a trailing symbol in an identifier name even though identifiers do not support + as a symbol.
< IDENTIFIER: <LETTER> (<LETTER>|<DIGIT>)* (["+"])? >
There are probably some optimizations that should be performed so that any outer join expression between two outer-joined tables is used only once in a query. There may also be prefetching issues as well for outer join expressions, which again might be solved by using each outer join expression only once.