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]
