db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lionel PASQUIER" <lpasqu...@omnikles.com>
Subject join and criterion
Date Tue, 27 Apr 2004 09:22:22 GMT
Hello !

I have difficulties to figure out how to create a request with torque on my (almost) complicated
problem. After a few search on the list it appears I have to use Criterion, and not only simple
criteria calls.

Here is the description of my DB:


table A with fields : IDA and IDversion
table B with the field: IDB
table C with fields : IDC and IDversion

A and B are linked by a N/N table AB with fields : IDA and IDB
C and B are linked by a N/N table CB with fields : IDC and IDB

What I want:
I have a list of IDversion. I want to retrieve all the IDB that are linked to a A with the
IDversion in the list OR linked to a C with the IDversion in the same list.

the SQL request I want would look like:
SELECT DISTINCT B.IDB
FROM B, A, AB, C, CB WHERE 
(
   (
	A.IDversion IN ('16','15') AND B.IDB=AB.IDB AND AB.IDA=A.IDA
   ) 
   OR 
   (
   	C.IDversion IN ('16','15') AND B.IDB=CB.IDB AND CB.IDC=C.IDC
   )
)

So I made the next criteria with torque:
Criteria crit = new Criteria();
Criteria.Criterion AIdVersion = crit.getNewCriterion(APeer.IDversion, versionList, Criteria.IN);
Criteria.Criterion CIdVersion = crit.getNewCriterion(CPeer.IDversion, versionList, Criteria.IN);
Criteria.Criterion joinBA_B = crit.getNewCriterion(BPeer.IDB , ABPeer.IDB, Criteria.EQUAL);
Criteria.Criterion joinAA_B = crit.getNewCriterion(ABPeer.IDA , APeer.IDA, Criteria.EQUAL);
Criteria.Criterion joinBC_B = crit.getNewCriterion(BPeer.IDB , CBPeer.IDB, Criteria.EQUAL);
Criteria.Criterion joinCC_B = crit.getNewCriterion(CBPeer.IDC , CPeer.IDC, Criteria.EQUAL);
crit.add(  (AIdVersion.and(joinBA_B).and(joinAA_B)
		).or(CIdVersion.and(joinBC_B).and(joinCC_B))
	   );

But unfortunatly, this gave me the following request:
SELECT DISTINCT B.IDB
FROM B, A, AB, C, CB WHERE 
(
	(
		A.IDversion IN ('16','15') AND B.IDB='AB.IDB' AND AB.IDA='A.IDA'
   ) 
   OR 
   (
   	C.IDversion IN ('16','15') AND B.IDB='CB.IDB' AND CB.IDC='C.IDC'
   )
)

And of course, the "'"s did not lead me to a join, and I got a parser error.

If I replace the Criteria.EQUAL by a Criteria.JOIN for the joinXX_X Criterion, it is even
weirder:
SELECT DISTINCT B.IDB
FROM B, A, AB, C, CB WHERE 
(
	(
		A.IDversion IN ('16','15') AND B.IDBJOIN'AB.IDB' AND AB.IDA JOIN 'A.IDA'
   ) 
   OR 
   (
   	C.IDversion IN ('16','15') AND B.IDBJOIN'CB.IDB' AND CB.IDC JOIN 'C.IDC'
   )
)
(the JOIN sticked to B.IDB and 'CB.IDB' is not an error of cut/paste...)

Does anyone can help?
I am using Torque 3.1 .

PS: if you managed to survive this email; I have a precision: at the end I'd may have a specific
IDB that I want to include to the request like:
Criteria.Criterion idBCrit = crit.getNewCriterion(BPeer.IDB , getIdB(), Criteria.EQUAL);
crit.add(
	     ( (AIdVersion.and(joinBA_B).and(joinAA_B)
		 ).or(CIdVersion.and(joinBC_B).and(joinCC_B))
	     ).and(idBCrit)
	   );


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


Mime
View raw message