You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Phil Warrick <ph...@mcgill.ca> on 2004/06/05 16:26:39 UTC
query: (not) exists
Hi Jakob,
Each time I try something a little different, I find a challenge!
Consider again the model of PathTest:
A-1----M-B-1----M-C-1----1-D
|
C1
The query "Find all As having a B with a particular C-D combination"
works fine:
// d1 criteria
Criteria crit1 = new Criteria();
crit1.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
Query query = new QueryByCriteria(A.class, crit1);
Collection allAs = broker.getCollectionByQuery(query);
But what if I want an exclusion criteria: "Find all As having a B
_without_ a particular C-D combination"
A notEqualTo criteria is not correct:
crit1.addNotEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
since there could be many C's belonging to a B that satisfy this criteria.
I think that this leaves the approaches of Criteria#exists() and
Criteria#notExists(), but they require query arguments. And it looks
like this only works for report queries?
Of course, things get more complicated fast: "Find all As having a B
with a particular C-D combination c1-d1 and _without_ another C-D
combination c2-d2". I would have hoped that the inclusion could have
been a parent criteria and the exclusion a child criteria (related by
Criteria.PARENT_QUERY_PREFIX):
// subquery
Criteria subCrit = new Criteria();
subCrit.addEqualTo(Criteria.PARENT_QUERY_PREFIX + "bSet.cSet.D.dAttrib",
new Integer("10010"));
subCrit.setAlias("subAlias", "cSet.d");
QueryByCriteria subQuery = new QueryByCriteria(A.class, subCrit);
// parent query
Criteria crit = new Criteria();
crit.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10011"));
crit.setAlias("alias", "edNodeSet.phenomenon");
crit.addNotExists(subQuery);
QueryByCriteria query = new QueryByCriteria(EventImpl.class, crit);
But the generated SQL is wrong: the resulting parent and child SQL
clauses do not share common table aliases.
Again a few experiments indicate that subqueries were not meant for
non-report queries. Is this true?
Have I missed another possible approach?
Is this a known limitation of subqueries that you have thought about?
Phil
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: query: (not) exists
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi phil,
i added a test case testSubQueryExists() for subquery using exists.
please have a look at it. this is the generated sql:
SELECT distinct A0.A_ATTRIB,A0.ID
FROM P_A_TABLE A0
INNER JOIN P_B_TABLE A1 ON A0.ID=A1.A_ID
INNER JOIN P_C_TABLE A2 ON A1.ID=A2.B_ID
INNER JOIN P_D_TABLE A3 ON A2.D_ID=A3.ID
WHERE (A3.D_ATTRIB = '10010')
AND NOT EXISTS (
SELECT B0.A_ATTRIB,B0.ID
FROM P_A_TABLE B0
INNER JOIN P_B_TABLE B1 ON B0.ID=B1.A_ID
INNER JOIN P_C_TABLE B2 ON B1.ID=B2.B_ID
INNER JOIN P_D_TABLE B3 ON B2.D_ID=B3.ID
WHERE A3.D_ATTRIB = '10011')
jakob
Jakob Braeuchi wrote:
> hi phil,
>
> the problem may be the alias for 'cSet.d' on the subquery. without the
> alias the sql of the sub-query references to parent-query alias A3 :
>
> //subquery
> Criteria subCrit = new Criteria();
> subCrit.addEqualTo(Criteria.PARENT_QUERY_PREFIX + "bSet.cSet.d.dAttrib",
> new Integer("10010"));
> // subCrit.setAlias("subAlias", "cSet.d");
> QueryByCriteria subQuery = new QueryByCriteria(A.class, subCrit);
>
> //parent query
> Criteria crit = new Criteria();
> crit.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10011"));
> // crit.setAlias("alias", "cSet.d");
> crit.addNotExists(subQuery);
> QueryByCriteria query = new QueryByCriteria(A.class, crit);
>
> Collection result = broker.getCollectionByQuery(query);
>
> SELECT A0.A_ATTRIB,A0.ID
> FROM P_A_TABLE A0
> INNER JOIN P_B_TABLE A1 ON A0.ID=A1.A_ID
> INNER JOIN P_C_TABLE A2 ON A1.ID=A2.B_ID
> INNER JOIN P_D_TABLE A3 ON A2.D_ID=A3.ID
> WHERE (A3.D_ATTRIB = '10011')
> AND NOT EXISTS (
> SELECT B0.A_ATTRIB,B0.ID
> FROM P_A_TABLE B0
> INNER JOIN P_B_TABLE B1 ON B0.ID=B1.A_ID
> INNER JOIN P_C_TABLE B2 ON B1.ID=B2.B_ID
> INNER JOIN P_D_TABLE B3 ON B2.D_ID=B3.ID
> WHERE A3.D_ATTRIB = '10010')
>
> when i use an alias on both queries the sql fails !
>
> jakob
>
>
> Jakob Braeuchi wrote:
>
>> hi phil,
>>
>> subqueries were coming quite late and were mainly used for
>> report-queries but imo they should also work for queryByCriteria. i'll
>> check this asap.
>>
>> jakob
>>
>> Phil Warrick wrote:
>>
>>> Hi Jakob,
>>>
>>> Each time I try something a little different, I find a challenge!
>>>
>>> Consider again the model of PathTest:
>>>
>>> A-1----M-B-1----M-C-1----1-D
>>> |
>>> C1
>>>
>>> The query "Find all As having a B with a particular C-D combination"
>>> works fine:
>>>
>>> // d1 criteria
>>> Criteria crit1 = new Criteria();
>>> crit1.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
>>> Query query = new QueryByCriteria(A.class, crit1);
>>> Collection allAs = broker.getCollectionByQuery(query);
>>>
>>> But what if I want an exclusion criteria: "Find all As having a B
>>> _without_ a particular C-D combination"
>>>
>>> A notEqualTo criteria is not correct:
>>>
>>> crit1.addNotEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
>>>
>>> since there could be many C's belonging to a B that satisfy this
>>> criteria.
>>>
>>> I think that this leaves the approaches of Criteria#exists() and
>>> Criteria#notExists(), but they require query arguments. And it looks
>>> like this only works for report queries?
>>>
>>> Of course, things get more complicated fast: "Find all As having a B
>>> with a particular C-D combination c1-d1 and _without_ another C-D
>>> combination c2-d2". I would have hoped that the inclusion could have
>>> been a parent criteria and the exclusion a child criteria (related by
>>> Criteria.PARENT_QUERY_PREFIX):
>>>
>>> // subquery
>>> Criteria subCrit = new Criteria();
>>> subCrit.addEqualTo(Criteria.PARENT_QUERY_PREFIX +
>>> "bSet.cSet.D.dAttrib", new Integer("10010"));
>>> subCrit.setAlias("subAlias", "cSet.d");
>>> QueryByCriteria subQuery = new QueryByCriteria(A.class, subCrit);
>>>
>>> // parent query
>>> Criteria crit = new Criteria();
>>> crit.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10011"));
>>> crit.setAlias("alias", "edNodeSet.phenomenon");
>>> crit.addNotExists(subQuery);
>>> QueryByCriteria query = new QueryByCriteria(EventImpl.class, crit);
>>>
>>> But the generated SQL is wrong: the resulting parent and child SQL
>>> clauses do not share common table aliases.
>>>
>>> Again a few experiments indicate that subqueries were not meant for
>>> non-report queries. Is this true?
>>>
>>> Have I missed another possible approach?
>>>
>>> Is this a known limitation of subqueries that you have thought about?
>>>
>>> Phil
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: query: (not) exists
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi phil,
the problem may be the alias for 'cSet.d' on the subquery. without the alias the
sql of the sub-query references to parent-query alias A3 :
//subquery
Criteria subCrit = new Criteria();
subCrit.addEqualTo(Criteria.PARENT_QUERY_PREFIX + "bSet.cSet.d.dAttrib", new
Integer("10010"));
// subCrit.setAlias("subAlias", "cSet.d");
QueryByCriteria subQuery = new QueryByCriteria(A.class, subCrit);
//parent query
Criteria crit = new Criteria();
crit.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10011"));
// crit.setAlias("alias", "cSet.d");
crit.addNotExists(subQuery);
QueryByCriteria query = new QueryByCriteria(A.class, crit);
Collection result = broker.getCollectionByQuery(query);
SELECT A0.A_ATTRIB,A0.ID
FROM P_A_TABLE A0
INNER JOIN P_B_TABLE A1 ON A0.ID=A1.A_ID
INNER JOIN P_C_TABLE A2 ON A1.ID=A2.B_ID
INNER JOIN P_D_TABLE A3 ON A2.D_ID=A3.ID
WHERE (A3.D_ATTRIB = '10011')
AND NOT EXISTS (
SELECT B0.A_ATTRIB,B0.ID
FROM P_A_TABLE B0
INNER JOIN P_B_TABLE B1 ON B0.ID=B1.A_ID
INNER JOIN P_C_TABLE B2 ON B1.ID=B2.B_ID
INNER JOIN P_D_TABLE B3 ON B2.D_ID=B3.ID
WHERE A3.D_ATTRIB = '10010')
when i use an alias on both queries the sql fails !
jakob
Jakob Braeuchi wrote:
> hi phil,
>
> subqueries were coming quite late and were mainly used for
> report-queries but imo they should also work for queryByCriteria. i'll
> check this asap.
>
> jakob
>
> Phil Warrick wrote:
>
>> Hi Jakob,
>>
>> Each time I try something a little different, I find a challenge!
>>
>> Consider again the model of PathTest:
>>
>> A-1----M-B-1----M-C-1----1-D
>> |
>> C1
>>
>> The query "Find all As having a B with a particular C-D combination"
>> works fine:
>>
>> // d1 criteria
>> Criteria crit1 = new Criteria();
>> crit1.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
>> Query query = new QueryByCriteria(A.class, crit1);
>> Collection allAs = broker.getCollectionByQuery(query);
>>
>> But what if I want an exclusion criteria: "Find all As having a B
>> _without_ a particular C-D combination"
>>
>> A notEqualTo criteria is not correct:
>>
>> crit1.addNotEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
>>
>> since there could be many C's belonging to a B that satisfy this
>> criteria.
>>
>> I think that this leaves the approaches of Criteria#exists() and
>> Criteria#notExists(), but they require query arguments. And it looks
>> like this only works for report queries?
>>
>> Of course, things get more complicated fast: "Find all As having a B
>> with a particular C-D combination c1-d1 and _without_ another C-D
>> combination c2-d2". I would have hoped that the inclusion could have
>> been a parent criteria and the exclusion a child criteria (related by
>> Criteria.PARENT_QUERY_PREFIX):
>>
>> // subquery
>> Criteria subCrit = new Criteria();
>> subCrit.addEqualTo(Criteria.PARENT_QUERY_PREFIX +
>> "bSet.cSet.D.dAttrib", new Integer("10010"));
>> subCrit.setAlias("subAlias", "cSet.d");
>> QueryByCriteria subQuery = new QueryByCriteria(A.class, subCrit);
>>
>> // parent query
>> Criteria crit = new Criteria();
>> crit.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10011"));
>> crit.setAlias("alias", "edNodeSet.phenomenon");
>> crit.addNotExists(subQuery);
>> QueryByCriteria query = new QueryByCriteria(EventImpl.class, crit);
>>
>> But the generated SQL is wrong: the resulting parent and child SQL
>> clauses do not share common table aliases.
>>
>> Again a few experiments indicate that subqueries were not meant for
>> non-report queries. Is this true?
>>
>> Have I missed another possible approach?
>>
>> Is this a known limitation of subqueries that you have thought about?
>>
>> Phil
>>
>>
>>
>>
>>
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: query: (not) exists
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi phil,
subqueries were coming quite late and were mainly used for report-queries but
imo they should also work for queryByCriteria. i'll check this asap.
jakob
Phil Warrick wrote:
> Hi Jakob,
>
> Each time I try something a little different, I find a challenge!
>
> Consider again the model of PathTest:
>
> A-1----M-B-1----M-C-1----1-D
> |
> C1
>
> The query "Find all As having a B with a particular C-D combination"
> works fine:
>
> // d1 criteria
> Criteria crit1 = new Criteria();
> crit1.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
> Query query = new QueryByCriteria(A.class, crit1);
> Collection allAs = broker.getCollectionByQuery(query);
>
> But what if I want an exclusion criteria: "Find all As having a B
> _without_ a particular C-D combination"
>
> A notEqualTo criteria is not correct:
>
> crit1.addNotEqualTo("bSet.cSet.d.dAttrib", new Integer("10010"));
>
> since there could be many C's belonging to a B that satisfy this criteria.
>
> I think that this leaves the approaches of Criteria#exists() and
> Criteria#notExists(), but they require query arguments. And it looks
> like this only works for report queries?
>
> Of course, things get more complicated fast: "Find all As having a B
> with a particular C-D combination c1-d1 and _without_ another C-D
> combination c2-d2". I would have hoped that the inclusion could have
> been a parent criteria and the exclusion a child criteria (related by
> Criteria.PARENT_QUERY_PREFIX):
>
> // subquery
> Criteria subCrit = new Criteria();
> subCrit.addEqualTo(Criteria.PARENT_QUERY_PREFIX + "bSet.cSet.D.dAttrib",
> new Integer("10010"));
> subCrit.setAlias("subAlias", "cSet.d");
> QueryByCriteria subQuery = new QueryByCriteria(A.class, subCrit);
>
> // parent query
> Criteria crit = new Criteria();
> crit.addEqualTo("bSet.cSet.d.dAttrib", new Integer("10011"));
> crit.setAlias("alias", "edNodeSet.phenomenon");
> crit.addNotExists(subQuery);
> QueryByCriteria query = new QueryByCriteria(EventImpl.class, crit);
>
> But the generated SQL is wrong: the resulting parent and child SQL
> clauses do not share common table aliases.
>
> Again a few experiments indicate that subqueries were not meant for
> non-report queries. Is this true?
>
> Have I missed another possible approach?
>
> Is this a known limitation of subqueries that you have thought about?
>
> Phil
>
>
>
>
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org