How to get multiple counts from multiple tables?

Question

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),

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.

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.

Thanks in advance!

before you start. If you plan showing quite some pictures/collections and the aggregated sums... rather save them per image as a int column. When a User likes an image, update the like-count on that image and also store the like in the "likes" table as well for further data analysis. But SUMING over thousands of datasets just requires way to much power for large sets of data. (my humble opinion) — Najzero
– Najzero, Commented Jan 8, 2013 at 7:42
Btw, I just assume that you have a comment field as well in the comments table :) — SWeko
– SWeko, Commented Jan 8, 2013 at 7:46
@Najzero, I'm only showing 20 collections at a time, each of which will have on average five pictures. Do you think that's too much? — Phil
– Phil, Commented Jan 8, 2013 at 7:47
@Phil, yeah, that would be nothing to be worrying about too much, BUT then you are cement fixed into that scenario (bad scalable). If your website gets super famous and your images get thousands of likes/comments, this will even hurt when only 20 images are displayed per page. There are some approaches like the "thing" and "data" (yep, only 2 tables for your complete database) that are awesomely fast when the application around is well designed... but for your issue. It still would not hurt saving some counts in int columns an update them upon action — Najzero
– Najzero, Commented Jan 8, 2013 at 7:56

Barranka · Accepted Answer · 2013-01-08 17:11:34Z

3

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:

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)

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

edited Jan 8, 2013 at 17:11

answered Jan 8, 2013 at 8:04

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ilmari Karonen Over a year ago

I think you'll need to use count(distinct ...) there, otherwise all the non-zero counts will come out equal to the product of the actual values (since you're basically doing an unrestricted cross join on the matching rows of the likes, views and comments tables).

user330315 Over a year ago

@IlmariKaronen: there is no cross join involved. The outer join will return NULL for those where no likes or views exists, and null will be ignored by count()

Ilmari Karonen Over a year ago

@a_horse_with_no_name: Yes, but consider the case where there are, for a given picture, e.g. 2 likes, 4 views and 3 comments. Then the group for that picture will consist of 2*4*3 = 24 rows, with no nulls, and all the counts will return 24 unless you use the distinct keyword.

Phil Over a year ago

@Barranka in the expanded collection version, in the three last left joins, should that be picture-id instead of pictureId (as that's what you have in the p.id as)?

Ilmari Karonen Over a year ago

@Phil: It should be enough to remove p.id from the group by clause, so that you just have group by c.id (and remove the p.id as picture_id part too, or maybe replace it with count(distinct p.id) as count_pictures).

|

Ilmari Karonen · Accepted Answer · 2013-01-08 08:14:54Z

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.)

Collectives™ on Stack Overflow

How to get multiple counts from multiple tables?

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related