translating a query on collections from SQL to nhibernate

I have a database schema, where I have a Product, Category, CategoryFeature, and an ProductCategoryFeatureValue.

The Model is mapped using Fluent NHibernate, but basically is as follows.

Product
-------
ID
Title

Category
--------
ID
Title

CategoryFeature
---------------
ID
CategoryID
Title

ProductCategoryFeatureValue
---------------
ID
ProductID
CategoryFeatureID
_______________________

Category [one] <-> [many] CategoryFeature
Product [many] <-> [many] 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!

I believe you want something like this in SQL

Select *
from Products p
where p.id in (
   select fv.ProductId
   from ProductCategoryFeatureValue fv
   where fv.CategoryFeatureID in (643229,643230)
   group by fv.ProductId
   having count(*)=@NumberOfDistinctFeaturesSelected 
)

That will stop you having to JOIN to the ProductCategoryFeatureValue table multiple times for every feature selected by the user. At the very least your going to get a nicer query plan. If you don't like the IN clause you could also use a temp table instead.

In terms of translating this into NHibernate it doesn't support any HAVING clause logic in the Criteria API but it is supported using HQL.

HQL Examples

var results = session.CreateQuery("from Product p where p.Id in (
    select fv.Product.Id
    from ProductCategoryFeatureValue fv
    where fv.CategoryFeature.Id in :featureids
    group by fv.Product.Id
    having count(fv)=:features
)")
.SetParameter("featureids", arrayOfFeatureIds)
.SetParameter("features", arrayOfFeatureIds.Count)
.List<Product>();

Not 100% sure from the question exactly what your mappings are but this may be close to what you need

object[] featureIds = new object[2];

featureIds[0] = 643229;
featureIds[1] = 667811;

ICriteria criteria = base.CreateCriteria(typeof(Product));
criteria.CreateAlias("ProductCategoryFeatureValueList", 
"ProductCategoryFeatureValue", JoinType.InnerJoin);
criteria.CreateAlias("ProductCategoryFeatureValue.CategoryFeatureID", 
"CategoryFeature", JoinType.InnerJoin);

criteria.Add(Expression.In("CategoryFeature.ID", featureIds));

If the "Expression.In" doesn;t quite do what you are after you could just do a quick loop adding

criteria.Add(Expression.Eq("CategoryFeature.ID", featureIds[i]));

I think your biggest problem is adding a condition on a join. I haven't tried it yet, but have been looking forward to the feature of NH 3.+ that lets you add a criteria join.

CreateAlias(string associationPath, string alias, JoinType joinType, ICriterion withClause) CreateCriteria(string associationPath, string alias, JoinType joinType, ICriterion withClause)