Re: An Object with Two objects in a relationship search query

From: urugn urugn (urug..ahoo.com)
Date: Thu Jul 19 2007 - 14:33:17 EDT

  • Next message: Mike Kienenberger: "Re: An Object with Two objects in a relationship search query"

    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