Re: Nested Queries

[Jakob Braeuchi]

hi wallace,

i do not have exactly your model, but i can query  for  persons using 
criteria from payments:

[Person] <-1--n-> [accounts] <-1--n-> [payments]

the following  sample asks for persons having an account with payments > 
500:

       crit = new Criteria();
       crit.addGreaterThan("konti.bewegungen.betrag", new BigDecimal(500));
       query = new QueryByCriteria(Person.class, crit, true); // DISTINCT
SELECT DISTINCT 
A0.isBoss,A0.gebDat,A0.idTest,A0.entryDate,A0.adresse,A0.idPartner,A0.name,A0.test,A0.vorname,A0.id 
FROM tabPerson A0
INNER JOIN tabKonto A1 ON A0.id=A1.idPerson
INNER JOIN tabBewegung A2 ON A1.id=A2.idKonto
WHERE A2.betrag >  '500'

i do not expect the proxies to be a problem, they do not affect sql 
building. but the exents may be a problem.
you said it worked without any extents. does is also work with extents 
either on A or B ?

do you probably have a testcase including sql-skripts, classes and 
repository ?

jakob

Gelhar, Wallace Joseph wrote:

Hi Jakob,

Are there any known issues with extents and nested queries?

My actual object model is as follows (somewhat simplified):

  [A] <-1--n-> [B] -1--1-> [C] -1--1-> [D]
  | |          | |
[A1] [A2]    [B1] [B2]
-All references and collections are proxied.
-All classes are referenced through their respective interface
The strange part is 
1.  I can query for C objects using a D field as criteria
2.  I can query for B (or B1) objects using a C field as criteria
3.  I can query for A objects using a B field as criteria
4.  I cannot query for A objects using C or D fields as criteria

I've made a simplified test case without interfaces, proxies or extents
and the query works as you described.  I'm still digging to try and
break it in a controlled manner.
Any thoughts?

Wallace J Gelhar
Facilities Planning & Management
Computer Information Systems
[EMAIL PROTECTED]
(715) 836-3411


-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 09, 2003 10:47 AM
To: OJB Users List
Subject: Re: Nested Queries

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