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

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.

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