db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brendan Richards" <bren...@designuk.com>
Subject FW: Multiple Joins problem.
Date Wed, 28 Apr 2004 16:50:26 GMT
Hi, I posted the below message earlier to users list but may be better
suited to Dev list... 


I've started looking through SqlQueryStatement.java to see how the SQL
is being built and it seems to look like this builds a 1-1 mapping from
"paths" (such as table.field) to TableAlias objects. This means that my
use of simplePropertys.name will only ever add one join. Any ways around
this?

(see previous message below)


-----Original Message-----
From: Brendan Richards 
Sent: 28 April 2004 16:06
To: ojb-user@db.apache.org
Subject: Multiple Joins problem.


Hi, I have an issue with making multiple joins between two tables. 

I have a basic contact table with a 1->many relationship to a
simpleProperty table - which consists of name-value pair records to
dynamically extend my data set.

If I query with just one simple property it works just fine:


PB code (select all contacts with a simpleproperty subscribed=true):

Criteria criteria = new Criteria();

criteria.addEqualTo("simplePropertys.name", "subscribed");
criteria.addEqualTo("simplePropertys.booleanValue", new Boolean(true));
    
int count = broker.getCount(new QueryByCriteria(ContactImpl.class,
criteria));





This causes the following SQL to run against the DB server: 

SELECT count(*) FROM Contact A0 INNER JOIN SimpleProperty A1 ON
A0.conOID=A1.simContactOID WHERE (A1.simName = @P1 ) AND
A1.simBooleanValue = @P2

Which is exactly what I want!


My problem starts if I want to query with two simplePropertys for
example, where subscribed=true AND region="Asia"


Criteria criteria = new Criteria();

    criteria.addEqualTo("simplePropertys.name", "subscribed");
    criteria.addEqualTo("simplePropertys.booleanValue", new
Boolean(true));
    
    
    Criteria criteria2 = new Criteria();
    criteria2.addEqualTo("simplePropertys.name", "region");
    criteria2.addEqualTo("simplePropertys.stringValue", "Asia");
    
     Criteria criteria3 = new Criteria();
     criteria3.addAndCriteria(criteria);
     criteria3.addAndCriteria(criteria2);

     int count3 = broker.getCount(new QueryByCriteria(ContactImpl.class,
criteria3));


This causes the following SQL to run:

SELECT count(*) FROM Contact A0 INNER JOIN SimpleProperty A1 ON
A0.conOID=A1.simContactOID WHERE ( (A1.simName = @P1 ) AND
A1.simBooleanValue = @P2 ) AND  ((A1.simName = @P3 ) AND
A1.simStringValue = @P4 )

This returns 0 results as it's only using one join for both properties. 


The SQL I need uses two joins like this:

SELECT     *
FROM         Contact INNER JOIN
                      SimpleProperty s1 ON Contact.conOID =
s1.simContactOID INNER JOIN
                      SimpleProperty s2 ON Contact.conOID =
s2.simContactOID
WHERE     (s1.simName = 'subscribed') AND (s1.simBooleanValue = 1) AND
(s2.simStringValue = 'Asia') AND (s2.simName = 'region')


In my application, criteria assembly is happening on-the-fly so I would
rather not use QueryBySQL if I can avoid it. Does anyone have any Ideas
on how to use the PersistenceBroker / Criteria API to build multiple
joins between the same tables as per the SQL example above?


Many thanks for your help, 


Brendan. 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message