Проектирование и запрос продукта / Система обзора

Question

Я создал систему продукта / обзора с нуля, и мне трудно выполнить следующий запрос в SQL Server.

В моей схеме есть разные таблицы: товары, обзоры, категории, фотографии товаров и марки. Я должен запросить их все, чтобы найти название бренда и категории, фотографии, средний рейтинг и количество отзывов.

Мне трудно получить количество отзывов и средний рейтинг.

Отзывы могут быть скрытыми (пользователь удалил) или заблокирован (ожидает модерации). В моей таблице товаров нет столбцов "Количество отзывов" или "Средний рейтинг", поэтому мне нужно посчитать их по этому запросу, но не считать заблокированные и скрытые (r.bloqueado=0 и r.hidden=0).

У меня есть запрос ниже, но он считает заблокированные и скрытые. Если я раскомментирую часть "and r.bloqueado=0 and r.hidden=0", я получу правильный подсчет, но тогда он не покажет товары, у которых есть 0 отзывов (что мне нужно!).

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"

Нужен ваш совет:

Я видел другие системы, которые имеют средний рейтинг и количество отзывов в таблице продуктов. Это очень помогло бы в сложности этого запроса (возможно, также и в производительности), но тогда мне придется делать дополнительные запросы в каждом новом обзоре, заблокированных и скрытых действиях. Я могу легко к этому. Учитывая, что включает и обновления происходит гораздо меньше, чем показ продуктов, это звучит хорошо. Было бы лучше сделать это?

Или лучше найти способ исправить этот запрос? Можете ли вы помочь мне найти решение?

Спасибо

Answer 1

Потерять записи очень легко, если объединить предложение where с внешним соединением. Строки, которые не существуют во внешней таблице, возвращаются как NULL. Ваш фильтр случайно исключил эти нули.

Вот пример, который демонстрирует, что происходит:

/* 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)
;

Внешнее объединение таблиц без предложения where возвращает:

запрос

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

Результат

ProductId   ReviewId    Blocked
1           1           0
2           NULL        NULL

Фильтрация для Blocked = 0 удалил бы вторую запись, и поэтому ProductId 2. Вместо этого:

-- 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
;

Этот запрос сохраняет значение NULL, и ProductId 2. Ваш пример немного сложнее, потому что у вас есть два поля.

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

Вам не нужно проверять оба поля на NULL, так как они отображаются в одной таблице.

Answer 2

Для подсчета количества товаров вы можете использовать case, когда и sum присваивая 1 там значение не r.bloqueado=0 или r.hidden=0 и 0 для этих значений (так что вы можете избежать фильтра где)

  "select top 20 p.id, p.brand, m.nome, c.name, sum(
                              case  when  r.bloqueado=0 then 0 
                                    when  r.hidden=0 then 0 
                                    else 1 
                              end )  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
  group by p.id, p.brand, p.modalidade, m.nome, c.name, f.id,f.nome"

для AVG может быть, вы можете сделать что-то подобное