public static List <Product> FindProductsByCategory(int categoryId, ProductSortByField sortBy)
{
ProductQuery p = new ProductQuery("p");
ProductCategoryQuery pc = new ProductCategoryQuery("pc");
vProductsSoldCountsQuery productsSold = new vProductsSoldCountsQuery("productsSold");
p.Select(p);
p.InnerJoin(pc).On(p.Id == pc.ProductId);
p.LeftJoin(productsSold).On(p.Id == productsSold.ProductId);
p.Where(p.IsActive == true);
p.Where(pc.CategoryId == categoryId);
if (!string.IsNullOrEmpty(sortBy.Field))
{
p.OrderBy(sortBy.Field,
sortBy.SortDirection == SortDirection.ASC
? esOrderByDirection.Ascending
: esOrderByDirection.Descending);
}
//string sql = p.Parse();
ProductCollection products = new ProductCollection();
products.Load(p);
return(products.Where(z => z.IsViewable == true).ToList());
}
public static List <Product> FindProductsByCategories(IList <int> categoryIds, ProductSortByField sortBy, bool matchAllCategories)
{
if (categoryIds.Count > 0)
{
ProductQuery p = new ProductQuery("p");
ProductCategoryQuery pc = new ProductCategoryQuery("pc");
vProductsSoldCountsQuery productsSold = new vProductsSoldCountsQuery("productsSold");
//p.es.CountAll = true;
//p.es.CountAllAlias = "TotalResultCount";
//p.es.PageNumber = 1;
//p.es.PageSize = 2;
p.es.Distinct = true;
p.Select(p);
p.InnerJoin(pc).On(p.Id == pc.ProductId);
p.LeftJoin(productsSold).On(p.Id == productsSold.ProductId);
p.Where(p.IsActive == true);
if (matchAllCategories)
{
ProductCategoryQuery pcAllCats = new ProductCategoryQuery("pcAll");
pcAllCats.Select(pcAllCats.ProductId)
.Where(pcAllCats.CategoryId.In(categoryIds.ToArray()))
.GroupBy(pcAllCats.ProductId)
.Having(pcAllCats.ProductId.Count() == categoryIds.Count);
p.Where(p.Id.In(pcAllCats));
}
else
{
p.Where(pc.CategoryId.In(categoryIds.ToArray()));
}
if (!string.IsNullOrEmpty(sortBy.Field))
{
p.OrderBy(sortBy.Field,
sortBy.SortDirection == SortDirection.ASC
? esOrderByDirection.Ascending
: esOrderByDirection.Descending);
}
string sql = p.Parse();
ProductCollection products = new ProductCollection();
products.Load(p);
return(products.Where(z => z.IsViewable == true).ToList());
}
return(new List <Product>());
}