Блог

Мне нужно подсчитать комментарии и голоса за проект, но комментарии считаются неправильно.

    SELECT projects . * , COUNT( votes.project_id ) AS votes, COUNT( comments.user_id) AS comments
    FROM  `projects` 
    LEFT JOIN  `votes` ON  `projects`.`id` =  `votes`.`project_id` 
    LEFT JOIN  `comments` ON  `projects`.`id` =  `comments`.`project_id` 
    WHERE  `votes`.`created_at` >  '2014-05-31 20:21:43' AND 
    GROUP BY  `projects`.`id` 
    ORDER BY  `votes` DESC 
  

Вывод:

Вам нужно подсчитывать разные значения, поэтому что-то вроде:

 SELECT projects . * , COUNT( DISTINCT votes.user_id ) AS votes, COUNT( DISTINCT comments.user_id) AS comments
FROM  `projects` 
LEFT JOIN  `votes` ON  `projects`.`id` =  `votes`.`project_id` 
LEFT JOIN  `comments` ON  `projects`.`id` =  `comments`.`project_id` 
WHERE  `votes`.`created_at` >  '2014-05-31 20:21:43' AND 
GROUP BY  `projects`.`id` 
ORDER BY  `votes` DESC 
  

Счетчик «неправильный», потому COUNT() что агрегат подсчитывает строки в результирующем наборе, а не строки из отдельных таблиц. Если бы у вас было две строки с project_id = 1 в comments таблице, оба агрегата count возвращали бы 8.

По сути, каждая строка from comments сопоставляется с каждой строкой from projects .

Существует несколько подходов к решению этой проблемы. Один из них — использовать подвыборку в списке ВЫБОРА, хотя это может быть дорогостоящим (с точки зрения производительности) для больших наборов:

 SELECT p.*
     , ( SELECT COUNT(1)
           FROM votes v
          WHERE v.project_id = p.project_id
            AND v.created_at > '2014-05-31 20:21:43'
       ) AS votes
     , ( SELECT COUNT(1)
           FROM comments c
          WHERE c.project_id = p.project_id
       ) AS comment_cnt
  FROM projects p
HAVING votes > 0
 ORDER BY votes DESC
  

(Предложение HAVING включено для эмуляции исходного запроса; в оригинале «внешность» ЛЕВОГО СОЕДИНЕНИЯ с таблицей голосов отрицается b предикатом on votes.created_at в предложении WHERE .)

Другой подход заключается в получении количества из каждой из таблиц отдельно (получите количество по project_id в двух отдельных запросах, на которые ссылаются как встроенные представления, а затем объедините эти подсчеты с операциями ОБЪЕДИНЕНИЯ. Например:

 SELECT p.*
     , w.votes
     , IFNULL(d.comment_cnt,0) AS comment_cnt
  FROM projects p
  JOIN ( SELECT v.project_id
              , COUNT(1) AS votes
           FROM votes v
          WHERE v.created_at > '2014-05-31 20:21:43'
          GROUP BY v.project_id
       ) w
    ON w.project_id = p.project_id
  LEFT
  JOIN ( SELECT c.project_id
               , COUNT(1) AS comment_cnt
           FROM comments c
          GROUP BY c.project_id
       ) d
    ON d.project_id = p.project_id
 ORDER BY w.votes DESC
  

(Поскольку спецификация должна была возвращать только строки, у которых количество «голосов» больше нуля, мы можем использовать ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ, чтобы исключить строки, у которых нет никаких «голосов». Для подсчета комментариев мы используем ВНЕШНЕЕ СОЕДИНЕНИЕ и просто заменяем любые нулевые значения нулями.

Есть и другие подходы.

Производительность будет зависеть от количества строк, мощности столбцов, на которые ссылаются ссылки, доступных индексов, плана выполнения, выбранного оптимизатором, и так далее.