db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <florianf...@compuserve.de>
Subject Re: join and criterion
Date Wed, 28 Apr 2004 06:16:22 GMT
Hi Lionel,

instead of Criteria.and try Criteria.addJoin or use  
Criteria.JOIN instead of Criteria.EQUAL as SQL-Comparator.

To follow your example:
...
Criteria.Criterion joinBA_B = crit.getNewCriterion(BPeer.IDB, 
ABPeer.IDB, Criteria.JOIN);
Criteria.Criterion joinAA_B = crit.getNewCriterion(ABPeer.IDA , 
APeer.IDA, Criteria.JOIN);
...

> 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