You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Brendan Richards <br...@designuk.com> on 2004/04/28 18:50:26 UTC

FW: Multiple Joins problem.

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

Re: FW: Multiple Joins problem.

Posted by Jakob Braeuchi <jb...@gmx.ch>.
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