php - 如何从多个表中获取多个计数？

Question

在网页上，我显示了一些图片集(我显示了每个图片集的缩略图)。每张图片都有五个相关的表格:

likes (id, user_id, picture_id),
views (id, user_id, picture_id),
comments (id, user_id, picture_id, comment),

pictures (id (which equals the "picture_id" in the previous tables), collection_id, picture_url and several other columns),
collections (id (equal to collection_id in previous table), and several other columns.

加载我的页面时，我需要汇总每个集合中所有图片的点赞数、浏览量和评论数，以便在每个集合下显示这些数字。

所以基本上:计算每张图片的点赞数，将它们全部加起来，显示数字。计算每张图片的浏览量，将它们全部计数，显示数量。统计每张图片的评论数，全部加起来，显示数量。然后冲洗并重复所有收集品。

我是 mysql 的新手，我在选择、多重连接、计数、php 与 mysql 等之间挣扎。我确信有很多方法可以做到这一点，但效率很低，所以我'希望你能告诉我最好/最快/最有效的方法。

提前致谢!

Answer 1

您可以使用选择和左连接来解决这个问题。

由于您将为每个 pictureId 计算每个表中的条目，因此您的 pictures 表将位于每个关系的左侧。所以:

select 
    p.id as pictureId,
    count(distinct l.id) as count_likes,
    count(distinct v.id) as count_views,
    count(distinct c.id) as count_comments
from
    pictures as p
    left join likes as l on p.id = l.pictureId
    left join views as v on p.id = v.pictureId
    left join comments as c on p.id = c.pictureId
group by
    p.id

基本上，您要为 pictures 表中的每条记录计算每个表中的每条记录；如果likes、views 或comments 中没有记录，则计数分别为零。

当然，您可以将这个想法扩展到集合:

select
    c.id as collection_id,
    p.id as picture_id,
    count(distinct l.id) as count_likes,
    count(distinct v.id) as count_views,
    count(distinct c.id) as count_comments
from
    collections as c
    left join pictures as p on c.id = p.collection_id
    left join likes as l on p.id = l.picture_Id
    left join views as v on p.id = v.picture_Id
    left join comments as c on p.id = c.picture_Id
group by
    c.id,
    p.id

如果你想为每个集合过滤你的结果，你只需要在group by之前添加where c.id = aValue(where aValue 是您要检索的集合 Id)

希望对你有帮助。

---

如果您只需要每个集合的汇总数据:

select
    c.id as collection_id,
    count(distinct l.id) as count_likes,
    count(distinct v.id) as count_views,
    count(distinct c.id) as count_comments
from
    collections as c
    left join pictures as p on c.id = p.collection_id
    left join likes as l on p.id = l.picture_Id
    left join views as v on p.id = v.picture_Id
    left join comments as c on p.id = c.picture_Id
group by
    c.id

这应该可以解决问题；-)