[jira] Commented: (CAY-560) Add support for outer joins

From: Mike Kienenberger (JIRA) ("Mike)
Date: Tue Apr 29 2008 - 17:39:52 EDT

  • Next message: Andrus Adamchik (JIRA): "[jira] Commented: (CAY-560) Add support for outer joins"

        [ https://issues.apache.org/cayenne/browse/CAY-560?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12830#action_12830 ]

    Mike Kienenberger commented on CAY-560:
    ---------------------------------------

    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);
    + }

    > Add support for outer joins
    > ---------------------------
    >
    > Key: CAY-560
    > URL: https://issues.apache.org/cayenne/browse/CAY-560
    > Project: Cayenne
    > Issue Type: New Feature
    > Components: Cayenne Core Library
    > Reporter: Øyvind Harboe
    > Assignee: Mike Kienenberger
    > Fix For: 1.2 [STABLE]
    >
    > Attachments: 1.2-outerjoin-null-2006-09-05-01.txt, cayenne-2.0-patch-outerjoin-null-audit-customexp.txt
    >
    >
    > Currently outer joins are not supported.
    > See thread:
    > http://objectstyle.org/cayenne/lists/cayenne-user/2006/05/0165.html
    > // This Java code will result in the SQL query immediately below...
    > SelectQuery query1=new SelectQuery(ElcRole.class);
    > query1.setQualifier(ExpressionFactory.matchExp(ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY + "." + ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()).
    > orExp(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY, getSysUser())));
    > List l=context.performQuery(query1);
    > Generates =>
    >
    > SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
    > FROM ELC_ROLE t0 LEFT OUTER JOIN
    > ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID = t1.ELCROLE_ID
    > WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
    > (t0.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881')
    > But should(?) have generated =>
    > SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
    > FROM ELC_ROLE t0 LEFT OUTER JOIN
    > ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID = t1.ELCROLE_ID
    > WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
    > (t0.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881')
    > 

    -- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    This archive was generated by hypermail 2.0.0 : Tue Apr 29 2008 - 17:43:46 EDT