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: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
