Re: Nested Queries

[Jakob Braeuchi]

hi wallace,

i can confirm that the extent in the ref-desriptor is a problem:

[Person] <-1--n-> [accounts] <-1--n-> [payments]    [account] has 
extents [account1]
                              
produces the following sql:

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
LEFT OUTER JOIN tabKonto A1 ON A0.id=A1.idPerson
LEFT OUTER JOIN tabBewegung A2 ON A1.id=A2.idKonto
LEFT OUTER JOIN tabKonto1 A1E0 ON A0.id=A1E0.idPerson
WHERE A2.betrag >  '500'

instead of  something like this:

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
LEFT OUTER JOIN tabKonto A1 ON A0.id=A1.idPerson
LEFT OUTER JOIN tabBewegung A2 ON A1.id=A2.idKonto
LEFT OUTER JOIN tabKonto1 A1E0 ON A0.id=A1E0.idPerson
LEFT OUTER JOIN tabBewegung A3 ON A1E0.id=A3.idKonto
WHERE A2.betrag >  '500'  or A3.betrag > '500'

the relationship from account to payment (tabKonto, tabBewegung) is only 
resolved for the concrete class not the extent.

jakob

Gelhar, Wallace Joseph wrote:

Hi Jakob,

I set up a test case with the following model:

  [A] <-1--n-> [B] -1--1-> [C] -1--1-> [D]
  | |                       | |
[A1] [A2]                [C1] [C2]
All queries are run against the A base class.  

If I map only the A extent, OJB executes two sql queries as expected,
one for the A1 extent and one for the A2 extent.
So I then mapped the C extent hierarchy with B having a reference
descriptor referencing the C base class.  This generated the following
incorrect SQL.
SELECT	A0.FIELD,
		A0.AID 
FROM tblA1 A0 INNER JOIN tblB A1 
	ON A0.AID=A1.AID 
LEFT OUTER JOIN null A2 
	ON A1.CID=A2.CID 
LEFT OUTER JOIN tblC2 A2E1 
	ON A1.CID=A2E1.CID 
LEFT OUTER JOIN tblC1 A2E0 
	ON A1.CID=A2E0.CID 
WHERE (field =  'one'  OR A2E1.FIELD =  'one'  OR A2E0.FIELD =  'one' )

However, if I map the B object to reference the C1 object instead of the
C base class, the sql generated is as expected.  So I've determined that
the problem occurs when an extent with an abstract base class or
interface is specified in the reference descriptor.  If I make the base
class concrete, the sql is generated correctly.
Wallace J Gelhar
Facilities Planning & Management
Computer Information Systems
[EMAIL PROTECTED]
(715) 836-3411


-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 11, 2003 1:32 PM
To: OJB Users List
Subject: Re: Nested Queries

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.na
me,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]
---------------------------------------------------------------------
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]