Badge query request: Received X likes in topic containing specific tag

data & reporting
1

Continuing the discussion from What cool badge queries have you come up with?.

Our forum has “Monthly Screenshots” threads. I’d like to reward the people who participate in these with a badge. I don’t know if there’s an easy way to tell if a post contains an image, but if so then that would be a welcome addition to the query.

The simplest query I can think of would be to award this badge to anyone who receives >1 like on a post in a topic tagged monthly-screenshots.

Why >1 likes? Because many people also reply to the image-posters, but these commenters very rarely receive more than 1 like (usually as a “thanks for the compliment” from an image-poster), so it’s accurate enough for my needs.

2 
select p.id, p.user_id, count(u.id)
from posts p
  left join post_uploads pu on p.id = pu.post_id
  left join uploads u on pu.upload_id = u.id 
where u.original_filename similar to '%.(jpg|png|)'
group by p.id, p.user_id
  having count(u.id) > 1

This finds all posts with at least two uploads that are images (end in either .jpg or .png).

4 Likes