Hi Andrus.
Thanks A lot for your responce.
Well i think coz of speed 1st option will be placed
1st. Altho i'll upgrade to 3 soon, that means i'll
still somehow encouter the second option. Once again
thanks, at least i know where to head to from here.
Regards Urugn.
--- Andrus Adamchik <andru..bjectstyle.org> wrote:
> 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.
>
>
____________________________________________________________________________________
Get the free Yahoo! toolbar and rest assured with the added security of spyware protection.
http://new.toolbar.yahoo.com/toolbar/features/norton/index.php
This archive was generated by hypermail 2.0.0 : Thu Jul 19 2007 - 14:33:42 EDT