Howto query objects they are not in many-to-many assoc

stundzig · **Joined:** Tue Nov 01, 2005 12:31 pm **Posts:** 5

Hi,

I've a Customers and Provisioners and a many-to-many assoc between them. Now I try to search all Customers, wich doesn't associated with provisioners. And thats with Criteria.

All what I found for it, is to query 'which customers have a association'. But not for the Negation.

The Restrictions.not doesnt work for it, see above. What I need, looks as SQL with subselect as follows:

Code:

select * from customer where customer.id not in (select customer_id from myassoc where provisioner_id not in (select provisioner_id from provisioner where name = 'blah')

An example criteria which is near to the solution, i think, is:

Code:

criteria = Utils.getCurrentSession().createCriteria(Customer.class);
criteria.createAlias("myassoc", "my" );
criteria.add(Restrictions.not(Restrictions.ilike("my.shortName", "blah")));

But this find's nothing, since it searches only for customers with a association that's not in the current type. And all customers without a association are completely ingored.

What I need is a left outer join or a Expression for the 'not in' subselect.

Could anyone point me to a solution or some docs where I could find more on this.

Regards
Steffen...

Hibernate version: 3.1rc3

hhuber · **Posted:** Tue Nov 22, 2005 10:00 am

I'm not sure, but maybe Restrictions.isEmpty and a corresponding condition on the collection might work.

Something like this:

Code:

crit.add( Restrictions.isEmpty( "subitems" ) ).createCriteria( "subitems" ).add( Restrictions.ne( "name", "blah" ) )

I think, it's worth a try.

hklasky · **Joined:** Fri Sep 03, 2004 9:11 am **Posts:** 6

I had a simmilar problem and the Restrictions.isEmpty solved my problem. Thanks.