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]
