mysql - 带条件的休眠子查询

Question

我如何才能将子查询加入/添加到条件中？我想做的是能够创建一个子查询来搜索 String Product，即 getCustomerProduct(两个表连接)。我还希望它能够进行 soundex 搜索。我的问题是，除非我运行它，否则我不确定我的子查询是否正确，如何将它添加到条件中？我是 nhiberate 的新手，感谢您的帮助!

   public IPagedList<Customer> GetSearchPagedCustomer(string product, string address, string county, int pagenumber, int pageSize)
     {
    ICriteria criteria = Session.CreateCriteria<Customer>();

                //.CreateAlias("Products", "cp")
                //.CreateAlias("cp.Product", "p");

                if (!string.IsNullOrEmpty(product))
                {
                    var getCustomerProduct = DetachedCriteria.For<Product>()
                        .SetProjection(Projections.Distinct(Projections.Property("Products.id")))
                        .CreateCriteria("Product", JoinType.InnerJoin)
                        .Add(Restrictions.Eq("Product.Name",product));

                    //criteria.Add(Restrictions.Like("Name", product));
                }            
                if (!string.IsNullOrEmpty(address))
                {
                    criteria.Add(Restrictions.Like("Address1", address, MatchMode.Anywhere) || Restrictions.Like("Address2", address, MatchMode.Anywhere) || Restrictions.Like("Address3", address, MatchMode.Anywhere));
                }
                if (!string.IsNullOrEmpty(county))
                {
                    criteria.Add(Restrictions.Like("County", county, MatchMode.Anywhere));
                }

return criteria.Future<Customer>().ToPagedList<Customer>(pageNumber, pageSize);
}

Answer 1

子查询应该是这样的:

DetachedCriteria userSubquery = DetachedCriteria.For<Product>("product")
     // here we need to join the Many-to-many table - to later project customer
     .CreateCriteria("CustomerProducts", "cp", JoinType.InnerJoin)
     // Filter the Subquery
     .Add(Restrictions.Eq("product.Name", product)) // alias must fit 
     // SELECT The Customer Id  - projection from the join
     .SetProjection(Projections.Property("cp.CustomerId"));

     // root Customer criteria, filtered by the subquery
     criteria
       .Add( Subqueries.PropertyIn("ID", userSubquery) );

请把它作为草稿，因为我不知道确切的映射...

也许还检查:

• Query on HasMany reference
• Hibernate Criteria for "in subselect" (休眠但是...)