You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by urugn urugn <ur...@yahoo.com> on 2007/07/19 17:19:08 UTC
An Object with Two objects in a relationship search query
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.
Re: An Object with Two objects in a relationship search query
Posted by urugn urugn <ur...@yahoo.com>.
Hay guys.
It would do good to have the patch. Am eager for one.
Mean while i kind of got to much used to DataObject
query strategies and am wondering if any one could
pass me a sample ROW SQL to use with SQLTemplate for
outer join queries or a link to a resolved issue on
the same. I tried something like this For Option 1.
String EXP = "SELECT
claim.CLAIM_ASSESSOR_UNIQUE,
claim.CLAIM_PRIORITY_CHEQUE, claim.CLAIM_CAUSE,
claim.CLAIM_NUMBER, claim.CLAIM_TYPE_UNIQUE,
claim.CLAIM_PRIORITY_CREDITNOTE,
claim.CLAIM_POST_DATE, claim.CLAIM_PRIORITY_DV,
claim.CLAIM_PRIORITY_HIGH, claim.CLAIM_ID,
claim.CLAIM_PRIORITY_INBOUND,
claim.CLAIM_INSURER_INTIMATION_DATE,
claim.CLAIM_INTIMATION_EMAIL,
claim.CLAIM_INTIMATION_FAX,
claim.CLAIM_INTIMATION_LETTER,
claim.CLAIM_INTIMATION_PHONE,
claim.CLAIM_INTIMATION_DATE,
claim.CLAIM_LETTER_UNIQUE, claim.CLAIM_LOSS_DATE,
claim.CLAIM_LOSS_ESTIMATE, claim.CLAIM_PRIORITY_LOW,
claim.CLAIM_MARINE_UNIQUE,
claim.CLAIM_PRIORITY_MEDIUM, claim.CLAIM_PARTICULAR,
claim.CLAIM_POLICY_UNIQUE, claim.CLAIM_REFERENCE,
claim.CLAIM_REPORTED_BY, claim.CLAIM_REPORTED_TO,
claim.CLAIM_TIME" +
" FROM EBROKER.EBROKER_CLAIMS claim,
EBROKER.EBROKER_CLAIMS_DETAILS claimDetail,
EBROKER.EBROKER_POLICIES policy,
EBROKER.EBROKER_MARINES marine,
EBROKER.EBROKER_CLIENTS client " +
" WHERE (claim.CLAIM_POLICY_UNIQUE =
policy.POLICY_ID AND policy.POLICY_CLIENT_UNIQUE =
client.CLIENT_ID) OR (claim.CLAIM_MARINE_UNIQUE =
marine.MARINE_ID AND marine.MARINE_CLIENT_UNIQUE =
client.CLIENT_ID) AND claimDetail.CLAIM_DETAIL_ID =
claim.CLAIM_ID ";
StringBuffer b = new StringBuffer();
b.append(EXP);
// b.append("policy.client.clientName
likeIgnoreCase $clientName and marine = null ");
// b.append("or policy = null and
marine.client.clientName likeIgnoreCase $clientName
");
b.append(" AND client.CLIENT_NAME LIKE
UPPER('"+searchField.getText().toLowerCase()+"%') ");
if(priorityCheck.isSelected()){
if(highPriorityRadio.isSelected())
b.append(" AND
claim.CLAIM_PRIORITY_HIGH =
"+highPriorityRadio.isSelected());
if(mediumPriorityRadio.isSelected())
b.append(" AND
claim.CLAIM_PRIORITY_MEDIUM =
"+mediumPriorityRadio.isSelected());
if(lowPriorityRadio.isSelected())
b.append(" AND
claim.CLAIM_PRIORITY_LOW =
"+lowPriorityRadio.isSelected());
if(dvPriorityRadio.isSelected())
b.append(" AND claim.CLAIM_PRIORITY_DV
= "+dvPriorityRadio.isSelected());
if(inboundPriorityRadio.isSelected())
b.append(" AND
claim.CLAIM_PRIORITY_INBOUND =
"+inboundPriorityRadio.isSelected());
if(creditNotePriorityRadio.isSelected())
b.append(" AND
claim.CLAIM_PRIORITY_CREDITNOTE =
"+creditNotePriorityRadio.isSelected());
if(chequePriorityRadio.isSelected())
b.append(" AND
claim.CLAIM_PRIORITY_CHEQUE =
"+chequePriorityRadio.isSelected());
}
if(postDateCheck.isSelected())
b.append(" AND claim.CLAIM_POST_DATE >
"+postAfterDateSelector.getDate()+" AND
claim.CLAIM_POST_DATE <
"+postBeforeDateSelector.getDate());
if(lossDateCheck.isSelected())
b.append(" AND claim.CLAIM_LOSS_DATE >
"+lossAfterDateSelector.getDate()+" AND
claim.CLAIM_LOSS_DATE <
"+lossBeforeDateSelector.getDate());
//settlement and recovery
b.append(" AND claimDetail.CLAIM_SETTLED =
"+settledCheck.isSelected()+" AND
claimDetail.CLAIM_RECOVERY =
"+recoveryCheck.isSelected());
SQLTemplate query = new
SQLTemplate(eBrokerClaim.class, b.toString());
List list = context.performQuery(query);
This kind of freezing my whole App. Men its sure
smooth to work with Object Entities than the above DB
entity hell.
The only other option is to provide gui controls that
wil create separate queries for a claim with a marine
and another query for a claim with a policy which i
think might be a little clumsy on the users side.
Also For Option 2. Is there any code changes that
might affect the current 2.0.2 setup after upgrading
to 3. If upgrading will resolve All these. Then that
will be th absolute option.
Regards Urugn
--- Mike Kienenberger <mk...@gmail.com> wrote:
> 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 <ur...@yahoo.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 <an...@objectstyle.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)
>
=== message truncated ===
____________________________________________________________________________________
Get the Yahoo! toolbar and be alerted to new email wherever you're surfing.
http://new.toolbar.yahoo.com/toolbar/features/mail/index.php
Re: An Object with Two objects in a relationship search query
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <ur...@yahoo.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 <an...@objectstyle.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
>
Re: An Object with Two objects in a relationship search query
Posted by urugn urugn <ur...@yahoo.com>.
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 <an...@objectstyle.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
Re: An Object with Two objects in a relationship search query
Posted by Andrus Adamchik <an...@objectstyle.org>.
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.