Hi Urugn,
The problem seems to be that you need OUTER joins for relationships
that may be NULL... Expressions are always resolved as INNER joins
and hence you are getting no results. We've been discussing a fix for
some time, but it is not there yet. For now you have two options:
1. Use SQLTemplate to run this as raw SQL, using OUTER joins (of
course you can get the objects back, just like with a SelectQuery).
2. Upgrade to Cayenne 3.0 M1 (up for the vote and due to be released
shortly) that offers some EJBQL support:
http://cayenne.apache.org/doc/ejbqlquery.html
EJBQL may look a hell lot like SQL, but operating similar to Cayenne
expressions (in object attribute and relationship terms), in fact it
spares you a lot of DB details:
String ejbql = "SELECT a "
+ "FROM eBrokerClaim a LEFT JOIN a.policy b LEFT
JOIN a. marine c"
+ "WHERE ...";
EJBQLQuery query = new EJBQLQuery(ejbql);
Andrus
On Jul 19, 2007, at 6:19 PM, urugn urugn 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 avoid querying the policy AND the marine inside the
> claim (which i want it to be policy OR marine). This brings no
> result beacuse as i said marine
> and policy cant exist together in a claim table row.
> Please help
>
> Regards Urugn
>
>
> ---------------------------------
> Pinpoint customers who are looking for what you sell.
This archive was generated by hypermail 2.0.0 : Thu Jul 19 2007 - 14:06:24 EDT