Difficulty checking for NULL / NOT_NULL collections

[Ken Robinson <kr...@computer-guidance.com>]

Hi all,

My basic model is as follows:

class A {
   int id; //PK
   ... //a few more attributes
   List b; //collection of class B objects, of which there can be 0:n
}

I'm having trouble creating the proper criteria to only return the instances
of A where
there are 1 or more instances of B associated (i.e. b.size > 0).  The same
goes
for the negative form.  I've tried OQL, PB with subqueries, and  PB with
exists/not exists
criteria.  About the only way I can get what I want is through QueryBySQL.
Is there
something that I'm overlooking here?

Thanks,

Ken Robinson

Re: Difficulty checking for NULL / NOT_NULL collections

[Jakob Braeuchi <jb...@gmx.ch>]

hi ken,

have you tried using in with subqueries:

ReportQueryByCriteria subQuery;
Criteria subCrit = new Criteria();
Criteria crit = new Criteria();
subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
subQuery.setColumns(new String[]{"productGroupId"});
subQuery.setDistinct(true);
crit.addNotIn("groupId", subQuery);
Query q = QueryFactory.newQuery(ProductGroup.class, crit);
results = broker.getCollectionByQuery(q);

the sample can be found in org.apache.ojb.broker.QueryTest#testSubQuery2()

i admit doesn't look straight forward but at least it works. if you have a
better/easier solution just let me know.

hth
jakob

----- Original Message -----
From: "Ken Robinson" <kr...@computer-guidance.com>
To: "OJB Users List (E-mail)" <oj...@jakarta.apache.org>
Sent: Friday, December 13, 2002 3:00 AM
Subject: Difficulty checking for NULL / NOT_NULL collections


> Hi all,
>
> My basic model is as follows:
>
> class A {
>    int id; //PK
>    ... //a few more attributes
>    List b; //collection of class B objects, of which there can be 0:n
> }
>
> I'm having trouble creating the proper criteria to only return the
instances
> of A where
> there are 1 or more instances of B associated (i.e. b.size > 0).  The same
> goes
> for the negative form.  I've tried OQL, PB with subqueries, and  PB with
> exists/not exists
> criteria.  About the only way I can get what I want is through QueryBySQL.
> Is there
> something that I'm overlooking here?
>
> Thanks,
>
> Ken Robinson
>
>
>
> --
> To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>