You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Lionel PASQUIER <lp...@omnikles.com> on 2004/04/27 11:22:22 UTC
join and criterion
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
Re: join and criterion
Posted by Thomas Edwin Santosa <ka...@telkom.net>.
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