I have a class Product which has a collection of Category classes, i.e. Product.Categories. The relationship is many to one with an intermediate table PRODUCT_CATEGORY_MAPPINGS. I want to be able to query products which have not just one but ALL of the categories I specifiy. For example, I would want to query for products that have the categories Computer, Notebook & WindowsXP. Up until now I have been unable to figure out how to code this with a criteria query or HQL. I managed to do the filter in code but it was quite inefficient. Is there a way to do this with a criteria query or HQL? Thanks.

From the reference manual:



This reduces to the Cats with kittens (this is a collection) which name starts with "F".

So in your case

Thanks for your reply. Actually that was one of the first things I tried but unfortunately it returns a superset of the data that I need. It returns products that have ANY rather than All of the categories. In the example I gave, products with the Notebook category would always also have the Computer category but there are other cases where that assumption is false. For example, I could have the categories Toy and Electronic, which are unrelated.

In SQL to select all or nothing you'd need to join to the Category table for each type and assign an alias. eg:



So for a Criteria you'd go with much the same. Try something like...

Thanks for your reply. I did also try something like that but NHibernate will not allow me to alias the same association twice.

NHibernate.QueryException: "duplicate association path: Categories"

I never actually tried it myself so sorry about that. The HQL would be:



But again not tested. If it doesn't work the SQL will, but I don't blame you for avoiding it. Personally for dynamic queries I try to avoid HQL and SQL.

I had a similar problem and solved it like this:

This criteria should retrieve a list of distinct category ids out of a supplied list of categories (catIdList) that have been assigned to a product:



Now, you can use this in a subquery. The number of selected categories hast to be less or equal to the "count" returned from the subquery:



My scenario was slightly different and I tried this out of my head, so maybe there're some errors, but the idea should work.

Thank you both cmaslen and wolli. I tried first the HQL because it was easier to understand, and it worked. I still can not quite understand what the Criteria query is doing but it works and it also seems to be a bit faster than the HQL, so I'm going to go with it for now and try to understand it as best I can. Thanks again.

I'll try to explain in more detail:

Let's assume, you want all products, that have assigned categories "Computer" and "XP" (and maybe more than that).

1) for each product, we count the number of categories out of this list, that have been assigned. Possible results are
0: none of the categories is assigned
1: either "Computer" or "XP" is assigned
2: both are assigned

2) No were are looking for all products, which have a count of 1) that is greater or equal to the number of categories that we're interested in:
count 1) >= 2
This will give us all products that have at least the wanted categories assigned.

1) is done in the deteached criteria, 2) is done in the subquery that uses the detached criteria.

Especially if you are looking for a lot of categories at once, that approach will be faster then building a join for each category.

That explains things quite clearly now. There is a possibility to search on several categories so not having multiple joins is definitely an advantage. Thanks a lot.