Or if you're using Cayenne 1.2, you can apply the outer join patch in
the JIRA database.
https://issues.apache.org/cayenne/browse/CAY-560
I just got back from a three-week vacation, but I'm hoping to port the
patch over to 2.0 and 3.0 in the next month if my schedule isn't too
busy.
On 7/19/07, urugn urugn <urug..ahoo.com> wrote:
> 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:39:11 EDT