when ojb builds the joins it checks if there's is already a join for a given path. that's why it produces only one join for multiple criteria.
if you want to force it to use multiple joins you'll have to define an alias on each criteria:
crit1.setAlias('subscribed');
crit2.setAlias('region');
afaik this feature is in the documentation.
hth jakob
Brendan Richards wrote:
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
