I’d like to share a badge that I developed for a contest in forum.kozovod.com.
Personal Page badge
- Created a topic in a particular category
- Wrote at least 1000 words in the topic (in one or multiple messages)
- Wrote in the topic at least 10 days (non-consecutive)
- Uploaded at least 10 images
In our forum, we have a category named “farms”. Goat farmers, small to big, are welcome to create their own topic/page there to tell about their farm, upload photos, push news and answer questions from those who buy milk or goats from them.
As part of the contest, we ask farmers to gain the “Personal Page” badge. This means they’ll have to create a topic in that category and write regularly for at least 10 days.
Hope there are many more applications for such “about me” pages, ours being just an example for a goat farmers community.
- Words are counted with using this quick and dirty word count SQL; a side-effect of such a simplicity is that e.g. an image would count for 4 words, but that shouldn’t be an issue when you need 1000 words;
- Images are counted with using the same simplistic approach
- Only words and images of the topic owner are counted in the topic
- Does not count:
- silenced users
- staged users
- archived topics
- closed topics
- non-visible topics (hidden from lists)
- deleted posts
- Run daily
WITH personal_pages AS ( SELECT * FROM topics WHERE category_id = 17 AND archived = false AND closed = false AND visible = true ), word_counts AS ( SELECT p.id as post_id, length(raw) - length(replace(raw, ' ', ''))+1 as num FROM posts p ), image_count AS ( SELECT p.id as post_id, length(raw) * 2 - length(replace(raw, '<img', '123')) - length(replace(raw, '[img]', '1234')) as num FROM posts p ), summary AS ( SELECT pp.id as topic_id, pp.user_id as user_id, COUNT (DISTINCT p.created_at::date) as num_days, SUM (wc.num) as num_words, SUM (ic.num) as num_images FROM personal_pages pp LEFT JOIN users u on u.id = pp.user_id LEFT JOIN posts p ON p.topic_id = pp.id AND p.user_id = pp.user_id LEFT JOIN word_counts wc ON wc.post_id = p.id LEFT JOIN image_count ic ON ic.post_id = p.id WHERE u.admin = false AND u.silenced = false AND u.staged = false AND p.deleted_at IS NULL GROUP BY pp.id, pp.user_id ) SELECT summary.*, CURRENT_DATE as granted_at FROM summary WHERE num_days >= 10 AND num_words >= 1000 AND num_images >= 10
Adjust your numbers at the very end of the query:
WHERE num_days >= 10 AND num_words >= 1000 AND num_images >= 10
… and put the category ID in this row:
WHERE category_id = 17
The query outputs more fields than required by the Badges System; that is for you to use the same query in the Data Explorer:
Your feedback is welcome.