CODEWITHSUNDEEP
mysql
Jed Grant
Jed Grant

Reputation: 1425

mysql - get count of tags AND IF the current user contributed to that count

I have no problem finding all tags for an object and getting the count for each tag using the following query.

SELECT *, COUNT(*) as count 
  FROM book_tags 
  JOIN tags ON book_tags.tag_id = tags.id 
  WHERE book_id = 66 GROUP BY tag_id

This works, but the user_id column gets mashed by the GROUP BY. I still want the tag totals for each tag on this book, but I ALSO want to create an alias that tells me whether or not the current user (user_id = 1) contributed to the count of each tag.

I am then going to display all tags the book has, but style the ones that this user added differently. I can do this will loops and multiple queries, it just seems like it should be doable in one.

Upvotes: 0

Views: 65

Answers (2)

Brian Hoover
Brian Hoover

Reputation: 7991

One way you could do this:

SELECT *, COUNT(*) as count,
  sum(if(user_id = 1, 1, 0)) as user_contributions
  FROM book_tags 
  JOIN tags ON book_tags.tag_id = tags.id 
  WHERE book_id = 66 GROUP BY tag_id

Upvotes: 0

Marc B
Marc B

Reputation: 360752

SELECT *, COUNT(*), SUM(user_id = 1) AS contributed
etc...

MySQL will convert the boolean true/false of the user_id = 1 comparison into an integer 1 or 0, which gets summed up. When you retrieve the query results, if it's non-zero, that particular user has contributed, and the value is the total number of contributions.

Upvotes: 1

Related Questions