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