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


(Erlend Sogge Heggen) #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.

Receive a badge for a post into a topic containing specific tag
(Jens Maier) #2
select, p.user_id, count(
from posts p
  left join post_uploads pu on = pu.post_id
  left join uploads u on pu.upload_id = 
where u.original_filename similar to '%.(jpg|png|)'
group by, p.user_id
  having count( > 1

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