Re: Nested Queries

[Jakob Braeuchi]

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
[EMAIL PROTECTED]
(715) 836-3411
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]