db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Edwin Santosa <kata...@telkom.net>
Subject Re: join and criterion
Date Tue, 27 Apr 2004 10:42:39 GMT
Pada Tuesday 27 April 2004 16:22, Lionel PASQUIER menulis:
> 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 had same problem with the "'". My workaround is somewhat extreme. I modified 
Criteria.java removing some "'" and it works for me. Perhaps other can give a 
better solution. I would love to hear it too.

Regards,
Thomas

> 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

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