Badges for posting

[PJH]

Since there deliberately isn’t the concept of achievements for posting, I’ve managed to implement it on ours, since we had it on our old forums and some were complain about the lack thereof.

Generic version:

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 1000

Change the 1000 for whatever post limit you’re after. I’ve themed ours (due to the nature of our board) on powers of 2.

Note that this will (should) only count posts that are public (not in categories that are restricted or private messages) or otherwise unrestricted (checkbox on edit category ignoring badges)

Additionally, for our board I’ve had to exclude certain topics due to the nature of my charges:

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE topic_id NOT IN (
   [elided]
) AND topic_id NOT IN (
   [elided]
) AND  (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) >= 2048

Edit: There was a problem with my queries - post_id is apparently required when this is triggered on post-related triggers.
See
https://meta.discourse.org/t/badges-sql-problem/19295

And

Creating triggered custom badge queries

This guide explains how to create triggered custom badge queries in Discourse, including the types of badges, constraints for triggered badges, and an example query. Required user level: Administrator This feature is disabled by default. To enable it, follow this guide. When defining badges in Discourse, you’ll encounter a “Trigger” option with the following choices: Update daily When a user acts on post When a user edits or creates a post When…

Edit 2: Added :backfill

[riking (Kane York)]

I think I have the correct trigger condition here, actually:

    :backfill OR
    user_id IN (
        SELECT trigger_post.user_id FROM posts trigger_post WHERE trigger_post.id IN (:post_ids)
    )

You get the user id list from the list of triggered posts and filter on that.

[ckshen]

@PJH

Since there deliberately isn’t the concept of achievements for posting, I’ve managed to implement it on ours, since we had it on our old forums and some were complain about the lack thereof.

Generic version:

SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id
HAVING count(*) > 1000

Thanks. This is very helpful. I am also thinking about encouraging users to initiate discussions. I am messing around your query to count original posts with p.post_number = 1

[AntiMetaman]

@PJH

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 1000

여기서 이 작은 보석을 발견했습니다. 특정 카테고리의 게시글 수에 대한 자체 커스텀 배지 쿼리를 만들고 싶었습니다. 특정 카테고리 ID 조건을 포함하여 해당 카테고리 내의 게시글만 집계되도록 수정하는 방법은 무엇일까요?

Some common badge queries

Enabling Badge SQL entails security and performance risks so it is not available by default. For more information, see: Enable Badge SQL Here are some common badge queries which you can use to create your own awesome badge! Grant a badge if user has created at least one topic in the “foo”* category *(“foo” is the category name, not the category slug) SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id FROM badge_posts p JOIN topics t ON t.id = p.topic_id WHERE cate…

여기서 특정 카테고리에 주제를 생성한 경우 배지가 수여되는 첫 번째 쿼리를 수정해야 할 것 같습니다. 차이점은 특정 카테고리의 게시글 수를 기준으로 수여한다는 점입니다.

[ckshen]

@PJH

일반적인 버전:

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 1000

배지 쿼리 미리 보기를 실행하면 다음과 같은 오류가 발생합니다:

There was an error with the query.  Any idea how to fix it?

Contract violation:
Query triggers on posts, but references the ':user_ids' array