Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (herse.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
  b=Le5W12ztZWchUwLAHclumZIrtDlXTwxbRwFuAb6JK/djwIrcaCvIhguOzHMAkNUOifbuxOGfOsWW7W8WpEQCpTCyyOqNEWVOQ95+2knUbBSmPcQJwJ6aGzeQAGsMkpC8FYFvBCWWq2UxS/OuaFZpJf45Ggnz64q6hIoaweGekcU=;
Date: Thu, 19 Jul 2007 11:33:17 -0700 (PDT)
From: urugn urugn <urugn@yahoo.com>
Subject: Re: An Object with Two objects in a relationship search query
To: user@cayenne.apache.org
In-Reply-To: <7A218178-3630-4B33-993E-9134F2EE78F4@objectstyle.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <691416.49415.qm@web36110.mail.mud.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 <andrus@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