Badge for posting image in specific topic: JoeZ proposes a badge idea where members earn a specific badge for posting a photo in a designated topic for a particular car make. JoeZ mentions that @meglio’s badge has similar functionality, but needs modification. JoeZ shares their work, utilizing Data Explorer to understand the database structure and create a query to pull users who have uploaded an image to a specific topic. The query can be updated to specify the topic ID for the badge and includes the granted_at field and post_ids.
I had an idea for a badge, one that’d work well for a site like @yetidragon http://forum.combustionpunks.co.uk/badges
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?
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