Re: Translating outer joins

From: Mike Kienenberger (mkienen..mail.com)
Date: Sat Aug 19 2006 - 19:44:36 EDT

  • Next message: Mike Kienenberger: "Idea for handling splits, possibly with only minor changes to alias table handling"

    On 8/19/06, Andrus Adamchik <andru..bjectstyle.org> wrote:
    > On Aug 18, 2006, at 11:30 PM, Mike Kienenberger wrote:
    > > how do you see the actual usage looking from the cayenne side?
    >
    > Let me rewrite the example I mentioned before in terms of proposed
    > SelectQuery API:
    >
    > SelectQuery q = new SelectQuery(Painting.class);
    > q.setQualifier("x.artistName = null or y.artistName = 'artist6'");
    >
    > // *** NEW SUGGESTED API
    > q.setJoins("LEFT OUTER JOIN toArtist x, LEFT OUTER JOIN toArtist y");
    > [...]
    > Essentially everything will continue to work the way it does now, and
    > only when you need custom splits and/or OUTER joins, "setJoins"
    > method will be used with a JPA join string.

    After thinking about this for awhile, I'm not sure I believe it :-)

    "x.artistName = null or y.artistName = 'artist6'" uses an absolute path.
    Current expressions use a relative path.

    "x.artistName = null or y.artistName = 'artist6'" in the current query
    language would mean relationship x's artist name and relationship y's
    artistName.

    But this now means Table x's artist name and Table y's artist name.

    It's possible I'm still confused since I feel like I haven't slept in a week :-)

    Let's consider some of my actual code. For the sql expression
    generated earlier, I have:

        List andedExpressionList = new ArrayList();

        andedExpressionList.add(ExpressionFactory.matchExp(Fee.FEE_TYPE_PROPERTY
    + "." + FeeType.DESCRIPTION_PROPERTY, FeeType.NAME_EDMS));

        List oredExpressionList = new ArrayList();

        oredExpressionList.add(ExpressionFactory.matchExp(Fee.FEE_CYCLE_PROPERTY
    + "." + FeeCycle.INITIAL_AUTHORIZATION_DOCUMENT_PROPERTY + "+" + "." +
    AuthorizationDocument.DEPENDENT_PERMIT_DOCUMENT_PROPERTY + "+" + "." +
    PermitDocument.AGENCY_PROPERTY, agency));

        oredExpressionList.add(ExpressionFactory.matchExp(Fee.FEE_CYCLE_PROPERTY
    + "." + FeeCycle.RECURRING_AUTHORIZATION_DOCUMENT_PROPERTY + "+" + "."
    + AuthorizationDocument.DEPENDENT_PERMIT_DOCUMENT_PROPERTY + "+" + "."
    + PermitDocument.AGENCY_PROPERTY, agency));

        andedExpressionList.add(constructOredExpression(oredExpressionList));

        Expression finalExpression = constructAndedExpression(andedExpressionList)

    That's really hard to read, so let me put it into a simplified string format:

    "( feeType.description = ? ) and ( (
    feeCycle.initialDoc.permitDoc.agency = ? ) or (
    feeCycle.recurringDoc.permitDoc.agency = ? ) )"

    initialDoc, permitDoc, and recurringDoc could all potentially be null
    and need to be outer joins.

    So we'd need something like this generated (assuming I understand how
    to do multiple joins):

    SELECT * FROM

    ENG_WORK_MGMT.FEE t0

    INNER JOIN ENG_WORK_MGMT.FEE_TYPE t1
      ON t0.FEE_TYPE_ID = t1.FEE_TYPE_ID

    INNER JOIN ENG_WORK_MGMT.FEE_CYCLE t2,
      ON t0.FEE_ID = t2.FEE_CYCLE_ID

    LEFT OUTER JOIN ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
      ON t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID

    LEFT OUTER JOIN ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
      ON t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID

    LEFT OUTER JOIN JOIN ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5,
      ON t2.FEE_CYCLE_ID = t5.INITIAL_FEE_CYCLE_ID

    LEFT OUTER JOIN ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
      ON t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID

    WHERE
        t1.DESCRIPTION = ?
     AND
        t4.AGENCY_ID = ?
     AND
        t6.AGENCY_ID = ?

    So our qualifier and joins strings would need to look like this:

    q.setJoins("INNER JOIN feeType ft, INNER JOIN feeCycle fc"
    + ",LEFT OUTER JOIN initialDoc id, LEFT OUTER JOIN permitDoc ipd"
    + ",LEFT OUTER JOIN recurringDoc rd, LEFT OUTER JOIN permitDoc rpd")

    q.setQualifier("( ft.description = ? ) and ( ( ipd.agency = ? ) or (
    rpd.agency = ? ) )'");

    The qualifier is no longer relative to the root entity -- each
    fragment is relative to the join alias.



    This archive was generated by hypermail 2.0.0 : Sat Aug 19 2006 - 19:45:02 EDT