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

On a webpage, I am displaying a number of picture collections (I show the thumbnails for each collection). Each picture has five relevant tables:

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

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

喜欢(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.

pictures(id(等于前面表格中的“picture_id”),collection_id,picture_url和其他几个列),集合(id(等于上表中的collection_id)和其他几列。

When loading my page, I need to aggregate the number of likes, views and comments for all pictures in each collection, so as to show those numbers under each collection.

在加载我的页面时,我需要聚合每个集合中所有图片的喜欢,视图和评论的数量,以便在每个集合下显示这些数字。

So basically: count the likes for each picture, count them all up, display number. Count the views for each picture, count them all up, display number. Count the comments for each picture, count them all up, display number. And then rinse and repeat for all collections.

所以基本上:计算每张图片的喜好,统计它们,显示数字。计算每张图片的视图,统计它们,显示数字。计算每张图片的评论,统计它们,显示数字。然后冲洗并重复所有收藏品。

I'm pretty new at mysql, and I'm struggling between selects, multiple joins, counts, php vs mysql, etc etc. I'm sure there's many ways I can do this that would be very inefficient, so I'm hoping you can tell me the best/fastest/most efficient way to do this.

我在mysql上很新,而且我在选择,多个连接,计数,php和mysql等之间挣扎。我相信我有很多方法可以做到这样效率非常低,所以我希望你可以告诉我最好/最快/最有效的方法。

Thanks in advance!

提前致谢!

2 个解决方案

#1

You can solve this with selects and left joins.

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

Since you'll count entries on each table for every pictureId, your pictures table will be the left side of each relation. So:

由于您将为每个pictureId计算每个表上的条目,因此您的图片表将是每个关系的左侧。所以:

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

Basically, you are counting every record in each table for each record in the pictures table; if there are no records in likes, views or comments, the count will be zero, respectively.

基本上,您正在计算图表中每个记录的每个表中的每条记录;如果喜欢,观点或评论中没有记录,则计数将分别为零。

Of course, you can expand this idea for collections:

当然,您可以扩展这个集合的想法:

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

If you want to filter your results for each collection, you only need to add where c.id = aValue before the group by (where aValue is the collection Id you want to retrieve)

如果要过滤每个集合的结果,只需在组之前添加c.id = aValue的位置(其中aValue是您要检索的集合ID)

Hope this helps you.

希望这对你有所帮助。

If you only need the aggregate data for each collection:

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

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

This should do the trick ;-)

这应该做的伎俩;-)

#2

You could do this with subselects:

您可以使用子选择执行此操作:

SELECT
  collections.*,
  ( SELECT COUNT(*) FROM pictures, likes
    WHERE pictures.id = likes.picture_id
      AND pictures.collection_id = collection.id
  ) AS like_count,
  ( SELECT COUNT(*) FROM pictures, views
    WHERE pictures.id = views.picture_id
      AND pictures.collection_id = collection.id
  ) AS view_count,
  ( SELECT COUNT(*) FROM pictures, comments
    WHERE pictures.id = comments.picture_id
      AND pictures.collection_id = collection.id
  ) AS comment_count
FROM collections
WHERE ...

This looks like it's going over the pictures table thrice, but I suspect that MySQL might be able to optimize that using the join buffer. I should note that I haven't actually tested this query, however. I also have no idea how this compares performance-wise with Barranka's LEFT JOIN solution. (Both would be pretty horrible if implemented naïvely, so it comes down to how smart MySQL's query optimizer is in each case.)

这看起来像是在图片表上翻了三次,但我怀疑MySQL可能能够使用连接缓冲区来优化它。但是,我应该注意到我实际上没有测试过这个查询。我也不知道这与Barranka的LEFT JOIN解决方案的性能比较。 (如果天真地实现,两者都会非常糟糕,所以它归结为MySQL的查询优化器在每种情况下的智能程度。)

#1