And this would end up looking something like this:
StringBuffer b = new StringBuffer();
b.append("policy+.client.clientName
likeIgnoreCase $clientName ");
b.append("or marine+.client.clientName
likeIgnoreCase $clientName ");
b.append("and claimDetail.settled = $settled
and claimDetail.recovery = $recovery ");
The "+" would indicate potentially null outer join clauses.
On 8/3/07, Mike Kienenberger <mkienen..mail.com> wrote:
> I'm pretty sure what you're doing is going to require an outer join.
>
> You'd need to be running Cayenne 1.2 with the outer join patch.
> Either that or you'll have to write the query using a raw sql
> template.
>
>
> On 7/18/07, urugn urugn <urug..ahoo.com> wrote:
> > Hi guys.
> >
> > Let me start by saying that am very greateful. This
> > coz of what the cay team has made things simple and
> > first.
> >
> > Am just having a small problem which i think might
> > have a simple solution, but can't figure it out. Am
> > trying to query from a table T1 that has two objects
> > T2 and T3 where if one object(lets say T2) is null the
> > other is not null. Each of this two objects contain a
> > similar object in them lets say T4 which i want to
> > access through a search query. Here is the code thats
> > is in place.
> >
> >
> >
> > StringBuffer b = new StringBuffer();
> > b.append("policy.client.clientName
> > likeIgnoreCase $clientName ");
> > b.append("or marine.client.clientName
> > likeIgnoreCase $clientName ");
> > b.append("and claimDetail.settled = $settled
> > and claimDetail.recovery = $recovery ");
> >
> > Expression e1 = Expression.fromString(b.toString());
> > java.util.Map map = new java.util.HashMap();
> > map.put("clientName",
> > searchField.getText().toLowerCase()+"%" );
> > Expression e2 = e1.expWithParameters(map);
> >
> > SelectQuery query = new
> > SelectQuery(eBrokerClaim.class, e2);
> >
> > List list = context.performQuery(query);
> > ......
> > ................
> >
> > I get the following debug.
> >
> > 02:12:18,888 INFO QueryLogger:423 - --- will run 1
> > query.
> > 02:12:18,889 INFO QueryLogger:377 - --- transaction
> > started.
> > 02:12:18,891 INFO QueryLogger:300 - SELECT
> > t0.CLAIM_ASSESSOR_UNIQUE, t0.CLAIM_PRIORITY_CHEQUE,
> > t0.CLAIM_CAUSE, t0.CLAIM_NUMBER, t0.CLAIM_TYPE_UNIQUE,
> > t0.CLAIM_PRIORITY_CREDITNOTE, t0.CLAIM_POST_DATE,
> > t0.CLAIM_PRIORITY_DV, t0.CLAIM_PRIORITY_HIGH,
> > t0.CLAIM_ID, t0.CLAIM_PRIORITY_INBOUND,
> > t0.CLAIM_INSURER_INTIMATION_DATE,
> > t0.CLAIM_INTIMATION_EMAIL, t0.CLAIM_INTIMATION_FAX,
> > t0.CLAIM_INTIMATION_LETTER, t0.CLAIM_INTIMATION_PHONE,
> > t0.CLAIM_INTIMATION_DATE, t0.CLAIM_LETTER_UNIQUE,
> > t0.CLAIM_LOSS_DATE, t0.CLAIM_LOSS_ESTIMATE,
> > t0.CLAIM_PRIORITY_LOW, t0.CLAIM_MARINE_UNIQUE,
> > t0.CLAIM_PRIORITY_MEDIUM, t0.CLAIM_PARTICULAR,
> > t0.CLAIM_POLICY_UNIQUE, t0.CLAIM_REFERENCE,
> > t0.CLAIM_REPORTED_BY, t0.CLAIM_REPORTED_TO,
> > t0.CLAIM_TIME FROM EBROKER.EBROKER_CLAIMS t0,
> > EBROKER.EBROKER_POLICIES t1, EBROKER.EBROKER_CLIENTS
> > t2, EBROKER.EBROKER_MARINES t3,
> > EBROKER.EBROKER_CLIENTS t4,
> > EBROKER.EBROKER_CLAIMS_DETAILS t5 WHERE
> > t0.CLAIM_POLICY_UNIQUE = t1.POLICY_ID AND
> > t1.POLICY_CLIENT_UNIQUE = t2.CLIENT_ID AND
> > t0.CLAIM_MARINE_UNIQUE = t3.MARINE_ID AND
> > t3.MARINE_CLIENT_UNIQUE = t4.CLIENT_ID AND t0.CLAIM_ID
> > = t5.CLAIM_DETAIL_ID AND (((UPPER(t2.CLIENT_NAME) LIKE
> > UPPER(?)) AND (t0.CLAIM_MARINE_UNIQUE IS NULL)) OR
> > ((t0.CLAIM_POLICY_UNIQUE IS NULL) AND
> > (UPPER(t4.CLIENT_NAME) LIKE UPPER(?)) AND
> > (t5.CLAIM_SETTLED = ?) AND (t5.CLAIM_RECOVERY = ?)))
> > [bind: 'p%', 'p%', 'false', 'false']
> > 02:12:18,897 INFO QueryLogger:351 - === returned 0
> > rows. - took 7 ms.
> > 02:12:18,898 INFO QueryLogger:384 - +++ transaction
> > committed.
> >
> > To explaim the above query.
> >
> > EBROKER.EBROKER_CLAIMS (eBrokerClaim) is my T1 that is
> > a child of EBROKER.EBROKER_POLICIES (eBrokerPolicy)
> > and EBROKER.EBROKER_MARINES (eBrokerMarine) related to
> > it (lets say T2 and T3 respectively) and T4 would be
> > EBROKER.EBROKER_CLIENTS (eBrokerClient).
> >
> >
> > Well i get no result.
> > My problem which i can actually see it is here.
> >
> > WHERE t0.CLAIM_POLICY_UNIQUE = t1.POLICY_ID AND
> > t1.POLICY_CLIENT_UNIQUE = t2.CLIENT_ID AND
> > t0.CLAIM_MARINE_UNIQUE = t3.MARINE_ID AND
> > t3.MARINE_CLIENT_UNIQUE = t4.CLIENT_ID
> >
> > from the above debug how can i make the second AND sql
> > statement to be an OR this is beacuse inside
> > eBrokerClaim, there is no way eBrokerPolicy and
> > eBrokerMarine can exist together in the same row. One
> > is null while the other is not null. Both contain
> > eBrokerClient of which am searching thru DataObject
> > path names.
> >
> > What would be the cayenne query statement i should use
> > to avid querying the policy AND the marine inside the
> > claim. This brings no result beacuse as i said marine
> > and policy cant exist together in a claim table row.
> > Please help
> >
> > Regards Urugn
> >
> >
> >
> >
> > ____________________________________________________________________________________
> > Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more.
> > http://mobile.yahoo.com/go?refer=1GNXIC
> >
>
This archive was generated by hypermail 2.0.0 : Fri Aug 03 2007 - 16:08:59 EDT