如何从特定类别显示前5个产品 [英] How do display top 5 products from specific category

问题描述

我想获取特定类别前5名最受欢迎的产品，例如计算机。

I want to fetch top 5 most popular product under specific category say computer.

这是我的类文件：

  public partial class Category {
       public int Id { get; set; }
       public string Name { get; set; }
       public int ParentCategoryId { get; set; } //reference to Id 
       public ICollection<Category> _subcategories;
  } 

  public partial class ProductCategory {
       public int Id { get; set; }
       public int ProductId { get; set; }
       public int CategoryId { get; set; }
       public virtual Category Category { get; set; }
       public virtual Product Product { get; set; }
  }

 public partial class Product {
       public int Id { get; set; }
       public string Name { get; set; }                     
       public int ProductViewcount { get; set; }//indicated how many times product has been viewed means most popular product.
 }

这里有一个包含记录的示例小提琴： http://www.sqlfiddle.com/#!3/20cba

Here a sample fiddle which contain records: http://www.sqlfiddle.com/#!3/20cba

最终输出：

ProductId ProductName

ProductId ProductName

1 hp

2 compaq

3 lenovo

这里的问题是计算机是我的主要类别和笔记本电脑是计算机的子类别，所以当我说获得前5名计算机产品时，我想检索子类别记录像小提琴一样，我想要获取所有笔记本电脑

Here problem is Computer is my main category and laptop is child category of Computer so when i say get top 5 Product of computer i want to retrieve child category records also like in fiddle i want to get all records of child category that is Laptop

我知道我必须按照 ProductViewCount 和获得前5名产品。

推荐答案

首先，需要。我们需要一个包含以下字段的表（视图）： ProductId ， ProductName ， ProductViewCount 和 RootCategoryId 。

First, let's define what we need. We need a table (view) with these fields: ProductId, ProductName, ProductViewCount, and RootCategoryId.

想象一下，类别表已经有 RootCategoryId 字段。然后我们可以使用这个查询来接收结果：

Imagine, the Category table has RootCategoryId field already. Then we can use this query to receive the result:

SELECT P.Id AS 'ProductId', P.Name AS 'ProductName', PVC.ProductViewCount, C.RootCategoryId
FROM Category C
  INNER JOIN ProductCategory PC ON PC.CategoryId = C.Id
  INNER JOIN Product P ON PC.ProductId = P.Id
  INNER JOIN ProductViewCount PVC ON P.Id = PVC.ProductId

不幸的是，类别表没有必要的字段。因此，我们需要一个表（而不是类别），其中包含字段 CategoryId 和 RootCategoryId 。

Unfortunately the Category table hasn't necessary fields. So we need a table (instead of Category) with fields CategoryId and RootCategoryId.

对于顶级类别 CategoryId 和 RootCategoryId 是相同的：

For top categories CategoryId and RootCategoryId are the same:

SELECT Id AS 'CategoryId', Id AS 'RootCategoryId'
FROM Category
WHERE ParentCategoryId = 0

对于后代类别 CategoryId 是 Id ，而 RootCategoryId 与父级相同。所以我们可以写CTE：

For descendant categories CategoryId is the Id, and RootCategoryId is the same as the parent. So we can write CTE:

WITH RecursiveCategory(CategoryId, RootCategoryId)
AS
(
    SELECT Id AS 'CategoryId', Id AS 'RootCategoryId'
    FROM Category
    WHERE ParentCategoryId = 0
    UNION ALL
    SELECT C.Id AS 'CategoryId', RC.RootCategoryId
    FROM Category C
        INNER JOIN RecursiveCategory RC ON C.ParentCategoryId = RC.CategoryId
)
. . .

现在让我们把它们放在一起。我们需要一个VIEW：

Now let's put the pieces together. We need a VIEW:

CREATE VIEW ProductWithRootCategory
AS
WITH RecursiveCategory(CategoryId, RootCategoryId)
AS
(
    SELECT Id AS 'CategoryId', Id AS 'RootCategoryId'
    FROM Category
    WHERE ParentCategoryId = 0
    UNION ALL
    SELECT C.Id AS 'CategoryId', RC.RootCategoryId
    FROM Category C
        INNER JOIN RecursiveCategory RC ON C.ParentCategoryId = RC.CategoryId
)
SELECT P.Id AS 'ProductId', P.Name AS 'ProductName', PVC.ProductViewCount, RC.RootCategoryId
FROM RecursiveCategory RC
  INNER JOIN ProductCategory PC ON PC.CategoryId = RC.CategoryId
  INNER JOIN Product P ON PC.ProductId = P.Id
  INNER JOIN ProductViewCount PVC ON P.Id = PVC.ProductId

现在您可以将视图添加到EF，并使用：

Now you can add the view to EF and use:

int rootCategoryId = 1; // Is's Computers
var productsInRootCategory = ProductWithRootCategory.Where(pwrc => pwrc.RootCategoryId == rootCategoryId);
var top5Products = productsInRootCategory.OrderBy(pwrc => pwrc.ProductViewCount).Take(5);

这篇关于如何从特定类别显示前5个产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！