Nested Queries

["Gelhar, Wallace Joseph" <ge...@uwec.edu>]

I am trying to execute a multiple nested PB query against an object
model such as follows:

[A] <-1--n-> [B] <-1--1-> [C] -1--1-> [D]

Criteria criteria = new Criteria();
criteria.addEqualTo("bCollection.c.d.value", "constant");
Query query = new QueryByCriteria(A.class, criteria);
Collection aCollection = Broker.getCollectionByQuery(query);
...

This should produce the SQL such as 

SELECT A.field
FROM A A0 INNER JOIN B A1
ON A0.fk = A1.pk
INNER JOIN C A2
ON A1.fk = A2.pk
INNER JOIN D A3
ON A2.fk = A3.pk
WHERE A3.value = 'constant'

But instead it produces only a single join

SELECT A.field
FROM A A0 INNER JOIN B A1
ON A0.fk = A1.pk
WHERE value = 'constant'

My question is if this type of multiple nested queries is supported?  Or
is only a single level of nesting supported?  How would you suggest
working around this problem?

Wallace J Gelhar
Facilities Planning & Management
Computer Information Systems
gelharwj@uwec.edu
(715) 836-3411


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

Re: Nested Queries

[Jakob Braeuchi <jb...@gmx.ch>]

hi wallace,

nested queries are supported:
 

[Konto] <-1--1-> [Person] <-1--n-> [Telefon]

the following queries all accounts (konto) with a balance > 100 belonging to a person with a phonenumber like 031%


     crit = new Criteria();
        crit.addGreaterThan("saldo", new BigDecimal(100));
        crit.addLike("inhaber.telefone.nummer", "031%");
        query = new QueryByCriteria(Konto.class, crit, true); // DISTINCT


SELECT DISTINCT A0.idPerson,A0.saldo,A0.nummer,A0.id FROM tabKonto A0
INNER JOIN tabPerson A1 ON A0.idPerson=A1.id
INNER JOIN tabTelefon A2 ON A1.id=A2.idPerson
WHERE (A0.saldo >  '100' ) AND A2.tel_nr LIKE  '031%'

hth
jakob

Gelhar, Wallace Joseph wrote:

>I am trying to execute a multiple nested PB query against an object
>model such as follows:
>
>[A] <-1--n-> [B] <-1--1-> [C] -1--1-> [D]
>
>Criteria criteria = new Criteria();
>criteria.addEqualTo("bCollection.c.d.value", "constant");
>Query query = new QueryByCriteria(A.class, criteria);
>Collection aCollection = Broker.getCollectionByQuery(query);
>...
>
>This should produce the SQL such as 
>
>SELECT A.field
>FROM A A0 INNER JOIN B A1
>ON A0.fk = A1.pk
>INNER JOIN C A2
>ON A1.fk = A2.pk
>INNER JOIN D A3
>ON A2.fk = A3.pk
>WHERE A3.value = 'constant'
>
>But instead it produces only a single join
>
>SELECT A.field
>FROM A A0 INNER JOIN B A1
>ON A0.fk = A1.pk
>WHERE value = 'constant'
>
>My question is if this type of multiple nested queries is supported?  Or
>is only a single level of nesting supported?  How would you suggest
>working around this problem?
>
>Wallace J Gelhar
>Facilities Planning & Management
>Computer Information Systems
>gelharwj@uwec.edu
>(715) 836-3411
>
>
>---------------------------------------------------------------------
>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