db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: query: (not) exists
Date Sun, 13 Jun 2004 15:11:59 GMT
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


Mime
View raw message