CODEWITHSUNDEEP
nhibernate
Remmus
Remmus

Reputation: 505

Can I write this SQL using Criteria API?

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

Upvotes: 0

Views: 136

Answers (1)

James Gregory
James Gregory

Reputation: 14223

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.

Upvotes: 1

Related Questions