Badge for posting image in specific topic


(Joe Z) #1

I had an idea for a badge, one that’d work well for a site like @yetidragon Combustionpunks

Assume a car forum. In this forum, there is a set topic for different makes, like Honda, Toyota, etc. If a member posts a PHOTO in the specific topic for the make, they get a specific badge for their make. Just posting a text reply grants them nothing.

@meglio has a badge that most of the functionality is there, but I’m too green to modify it to work for this idea.

Thoughts?


(Joe Z) #2

Please proof my work…I installed Data Explorer which dramatically helped me understand the database structure as well as the ability to try out some queries. From what I can tell, this will pull if you have uploaded an image to a specific topic.

set topic_id (6th line) as the topic for the badge.

SELECT DISTINCT ON (sub.user_id)
sub.user_id, sub.post_id, sub.created_at granted_at from (SELECT p.created_at, p.user_id, p.id as post_id,
    length(raw) * 2
      - length(replace(raw, '<img', '123'))
      - length(replace(raw, '[img]', '1234')) as num
  FROM posts p where topic_id=62) sub
  where sub.num > 0 AND (:backfill OR sub.post_id IN (:post_ids) )

Updated. Needed to have the ‘granted_at’ as well as post_ids