[prev in list] [next in list] [prev in thread] [next in thread] 

List:       ojb-user
Subject:    Re: Multiple Joins problem.
From:       Jakob Braeuchi <jbraeuchi () gmx ! ch>
Date:       2004-04-28 16:54:01
Message-ID: 408FE1A9.4030007 () gmx ! ch
[Download RAW message or body]

hi brendan,

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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 

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

[prev in list] [next in list] [prev in thread] [next in thread]