Блог

У меня есть три таблицы.

• Публикации
• Изображения
• Голосов

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

Этот код работает для получения оценки записи

 SELECT 
    posts.*, 
    COALESCE(SUM(votes.value), 0) as score
FROM posts 
    LEFT JOIN votes ON posts.post_id = votes.target_id
GROUP BY posts.post_id
  

и этот код работает для получения количества изображений вместе с сообщениями

 SELECT 
    posts.*, 
    COALESCE(COUNT(images.image_id), 0) as num_images
FROM posts 
    LEFT JOIN images ON posts.post_id = images.belongs_to_post
GROUP BY posts.post_id
  

но если я объединю эти две, чтобы получить

 SELECT 
    posts.*, 
    COALESCE(COUNT(images.image_id), 0) as num_images,
    COALESCE(SUM(votes.value), 0) as score
FROM posts 
    LEFT JOIN votes ON posts.post_id = votes.target_id
    LEFT JOIN images ON posts.post_id = images.belongs_to_post
GROUP BY posts.post_id
  

score умножается на количество изображений. Как я могу эффективно остановить это? Должен ли я группировать по другому столбцу?

Я использую MySQL 5.7.31.

Я бы рекомендовал коррелированные подзапросы:

 SELECT p.*, 
       (SELECT COUNT(*)
        FROM images i
        WHERE p.post_id = i.belongs_to_post
       ) as num_images,
       (SELECT COALESCE(SUM(v.value), 0)
        FROM votes v
        WHERE p.post_id = v.target_id
       ) as score
FROM posts p;
  

С индексами на images(belongs_to_post) и votes(target_id, value) это, вероятно, намного быстрее, чем любой подход, который использует агрегацию во внешнем запросе.