I have a database schema, where I have a `Product`, `Category`,
`CategoryFeature`, and an `ProductCategoryFeatureValue`.
Basically, the features available to a product are listed in the
`ProductCategoryFeatureValue` table, which is the 'middle-link' for
the many-to-many collection.
I need to create a query, where i can find all products, which have
ALL the features selected by the user.
Example, doing a search for two features with ids 643229 & 667811 in
SQL terms, I would do something like this:
SELECT * FROM Product
JOIN ProductCategoryFeatureValue AS feature1 ON Product.id =
feature1.ProductID AND feature1.categoryfeatureid = 643229
JOIN productcategoryfeaturevalue AS feature2 ON Product.id =
feature2.ProductID AND feature2.categoryfeatureid = 667811
Another query which I could do is this:
SELECT * FROM product WHERE
((SELECT id FROM productcategoryfeaturevalue AS feature1 WHERE
feature1.ItemGroupID = product.id AND feature1.categoryFeatureID =
643229 LIMIT 1) IS NOT NULL)
AND
((SELECT id FROM productcategoryfeaturevalue AS feature2 WHERE
feature2.ItemGroupID = product.id AND feature2.categoryFeatureID =
667811 LIMIT 1) IS NOT NULL)
Both have been tested and work well. However, I cannot seem to
reproduce them using NHibernate. Any ideas?
Thanks a lot in advance!
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
