db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Warrick <philip.warr...@mcgill.ca>
Subject query: (not) exists
Date Sat, 05 Jun 2004 14:26:39 GMT
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


Mime
View raw message