设计和查询产品/审查系统 [英] Designing and Querying Product / Review system

问题描述

我从头开始创建了一个产品/评论系统，我很难在SQL Server中执行以下查询。

我的架构有不同的表，适用于：产品，评论，类别，productPhotos和品牌。我必须查询所有这些信息，以找到品牌和类别名称，照片详细信息，平均评分和评论数量。

我很难获得No。评论和平均评分。

评论可以隐藏（用户已删除）或被阻止（等待审核）。我的产品表没有评论数或平均评分列，因此我需要在该查询中对其进行计数，而不是对被阻止和隐藏的计数（r.bloqueado = 0和r.hidden = 0）进行计数。 p>

我在下面有查询，但是它在计算被阻止和隐藏的内容。如果我取消对 and r.bloqueado = 0和r.hidden = 0部分的注释，则可以正确计数，但它不会显示具有0条评论的产品（这是我需要的！）。

 选择前20个
 p.id，p.brand，m.nome，c.name，
计数（r.product ）AS NoReviews，Avg（r.nota）AS AvgRating，
 f.id为cod_foto，f.nome为nome_foto 
从
 tbl品牌AS m 
内部加入
 （tblProducts AS p 
左联接
 tblProductsReviews AS r ON p.id = r.product）ON p.brand = m.id 
左联接
 tblProductsCategorias as c on p。 categoria = c.id 
左联接
（从
选择
 id_product，id，nome 
 tblProductsFotos O 
其中
 id =（SELECT min（I.id）
从tblProductsFotos I 
 W. 0 
 //问题-和r.bloqueado = 0和r.hidden = 0 
组，由
 p.id，p.brand，p.modalidade，m.nome，c.name，f.id，f.nome表示 
  

需要您的建议：

我见过其他具有产品表中的平均评分和评论数。这将大大提高此查询的复杂性（可能还会提高性能），但随后我必须在每个新审阅，阻止和隐藏的操作中执行额外的查询。我可以很容易地做到这一点。考虑到包含和更新发生的次数远少于显示产品，这听起来不错。
会是个更好的主意吗？

还是找到一种解决此查询的方法更好？您能帮我找到解决方案吗？

谢谢

解决方案

将where子句与外部联接组合在一起时，很容易丢失记录。外部表中不存在的行以NULL返回。您的过滤器意外地排除了这些空值。

以下示例演示了发生的情况：

  / *样本数据。 
 *有两个表：产品和评论。 
 *有两种产品：1& 2. 
 *只有产品1有评论。 
 * / 
声明@Product TABLE 
（
 ProductId INT 
）
; 
 
声明@检查表
（
 ReviewId INT，
 ProductId INT，
阻止的BIT 
）
; 
 
插入@Product 
（
 ProductId 
）
值
（1），
（2）
; 
 
插入@Review 
（
 ReviewId，
 ProductId，
 Blocked 
）
值
（1 ，1，0）
; 
  

外部联接表，不带where子句，返回：

查询

 -无处。 
选择
 p.ProductId，
 r.ReviewId，
 r。从产品b的
起封锁
 @产品AS p 
向左外部加入@Review AS r ON r.ProductId = p.ProductId 
; 
  

结果

  ProductId ReviewId已阻止
 1 1 0 
 2 NULL NULL 
  

过滤 Blocked = 0 会删除第二条记录，因此会删除 ProductId 2。 ：

 -随处可见。 
选择
 p.ProductId，
 r.ReviewId，
 r。从产品b的
起封锁
 @产品AS p 
向左外部加入@Review AS r ON r.ProductId = p.ProductId 
 WHERE 
 r.Blocked = 0 
或r.Blocked IS NULL 
; 
  

此查询保留NULL值和 ProductId 2.您的示例稍微复杂一点，因为您有两个字段。

  SELECT 
 ... 
 WHERE 
（
 Blocked = 0 
和隐藏= 0 
）
或已阻止IS NULL 
; 
  

您无需检查两个字段都为NULL，因为它们出现在同一表格中。

I created a product / review system from scratch and I´m having a hard time to do the following query in SQL Server.

My schema has different tables, for: products, reviews, categories, productPhotos and Brand. I have to query them all to find the brand and category name, photos details, Average Rating and Number of Reviews.

I´m having a hard time to get No. of reviews and average rating.

Reviews can be hidden (user has deleted) or blocked (waiting for moderation). My product table doesn't have No. of Reviews or Average Rating columns, so I need to count it on that query, but not counting the blocked and hidden ones (r.bloqueado=0 and r.hidden=0).

I have the query below, but it´s counting the blocked and hidden. If I uncomment the "and r.bloqueado=0 and r.hidden=0" part I get the right counting, but then it doesn't show products that has 0 reviews (something I need!).

select top 20 
    p.id, p.brand, m.nome, c.name, 
    count(r.product) AS NoReviews, Avg(r.nota) AS AvgRating, 
    f.id as cod_foto,f.nome as nome_foto 
from
    tblBrands AS m 
inner join 
    (tblProducts AS p 
left join 
    tblProductsReviews AS r ON p.id = r.product) ON p.brand = m.id 
left join 
    tblProductsCategorias as c on p.categoria = c.id 
left join 
    (select 
         id_product, id, nome 
     from 
         tblProductsFotos O 
     where 
         id = (SELECT min(I.id) 
               FROM tblProductsFotos I 
               WHERE I.id_product = O.id_product)) as f on p.id = f.id_product 
where 
    p.bloqueado = 0
    //Problem - and r.bloqueado=0 and r.hidden=0    
group by 
    p.id, p.brand, p.modalidade, m.nome, c.name, f.id,f.nome"

Need your advice:

I have seen other systems that has Avg Rating and No. of Reviews in the product table. This would help a lot in the complexity of this query (probably also performance), but then I have to do extra queries in every new review, blocked and hidden actions. I can easily to that. Considering that includes and updates occurs much much less than showing the products, this sounds nice. Would be a better idea to do that ?

Or is it better to find a way to fix this query ? Can you help me find a solution ?

Thanks

解决方案

It's very easy to lose records when combining a where clause with an outer join. Rows that do not exist in the outer table are returned as NULL. Your filter has accidentally excluded these nulls.

Here's an example that demonstrates what's happening:

/* Sample data.
 * There are two tables: product and review.
 * There are two products: 1 & 2.
 * Only product 1 has a review.
 */
DECLARE @Product TABLE
    (
        ProductId   INT
    )
;

DECLARE @Review TABLE
    (
        ReviewId    INT,
        ProductId   INT,
        Blocked     BIT
    )
;

INSERT INTO @Product
    (
        ProductId
    )
VALUES
    (1),
    (2)
;

INSERT INTO @Review
    (
        ReviewId,
        ProductId,
        Blocked
    )
VALUES
    (1, 1, 0)
;

Outer joining the tables, without a where clause, returns:

Query

-- No where.
SELECT
    p.ProductId,
    r.ReviewId,
    r.Blocked
FROM
    @Product AS p   
        LEFT OUTER JOIN @Review AS r        ON r.ProductId = p.ProductId
;

Result

ProductId   ReviewId    Blocked
1           1           0
2           NULL        NULL

Filtering for Blocked = 0 would remove the second record, and therefore ProductId 2. Instead:

-- With where.
SELECT
    p.ProductId,
    r.ReviewId,
    r.Blocked
FROM
    @Product AS p   
        LEFT OUTER JOIN @Review AS r        ON r.ProductId = p.ProductId
WHERE
    r.Blocked = 0
    OR r.Blocked IS NULL
;

This query retains the NULL value, and ProductId 2. Your example is a little more complicated because you have two fields.

SELECT
    ...
WHERE
    (
        Blocked = 0
        AND Hidden = 0
    )
    OR Blocked IS NULL
;

You do not need to check both fields for NULL, as they appear in the same table.

这篇关于设计和查询产品/审查系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！