Can I write this SQL using Criteria API?

Question

I am trying to reproduce this query in Criteria API. In my mappings I have a many to many between Tag and Product, but this only appears in my Tag class, i.e. the Product class has no knowledge of tags. Basically I want to find all the products that have all of a list of tags. Here is the sql I produced although it is probably possible to improve.

SELECT Product.* FROM Product

    WHERE Product.ProductID IN (
     SELECT ProductID FROM ProductTagMap
      INNER JOIN Tag ON ProductTagMap.TagId = Tag.TagId
       WHERE Tag.UrlName = 'sticks')

    AND Product.ProductID IN (
     SELECT ProductId FROM ProductTagMap
      INNER JOIN Tag ON ProductTagMap.TagId = Tag.TagId
       WHERE Tag.UrlName = 'vic-firth')

Any Ideas, much appreciated

Answer 1

Yes, you're probably best looking at DetachedCriteria's. It's not exactly what you're doing, but this blog post has some good examples of using the DetachedCriteria - just replace his Exists with an In instead.