db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: FW: Multiple Joins problem.
Date Wed, 28 Apr 2004 16:57:43 GMT
hi brendan,

use Criteria#setAlias('anAlias') on each Criteria.

jakob

Brendan Richards wrote:

> 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
> 
> 

---------------------------------------------------------------------
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