I want to create a custom badge in which these rules will be added, which simply means that when user accomplish these below things then the particular related badge will be given to them automatically.
Users need to accomplish these below things/Rules -
User should have created at least 2 posts
Uses should have uploaded profile pic
User should have liked at least one post
Note- I have seen some badges creation with the help of SQL queries, actually I am very new to this so please kindly tell me where to put SQL queries (if required) to achieve these above rules.
I have installed the plugin and made the below SQL command which is giving me the users who are fulfilling these 3 rules but I don’t know how to convert it into a trigger when an user acts on the post.
SQL command - select users.username, user_stats.user_id from user_stats Inner Join users ON user_stats.user_id = users.id where post_count = 2 AND likes_given = 1 AND uploaded_avatar_id > 0
This is a SQL code of “Trigger: Update daily”;
SELECT st.user_id user_id, current_timestamp granted_at
FROM user_stats st
JOIN users u
ON st.user_id = u.id
WHERE st.post_count >= 2 AND st.likes_given >= 1 AND u.uploaded_avatar_id > 0
The other triggers depend on something specific, and I don’t recommend using them unless you find a SQL expert. (I’m not familiar with SQL myself
Do you see the FROM user_stats st line? That’s telling the query that st is short for the user_stats table
The same is true in JOIN statement where it says users u. It is saying that in query u is an alias for the users table in that query. Aliases are used to allow simplifying queries to use less typing which reduces the chances of errors.
With regards to 3, if you enable that option then if a user no longer qualifies for a badge based on the query it will remove the badge from their account.
The query runs every 24 hours based on what time of day you started the container. So I have one instance that runs it around 0:00UTC (roughly) and another instance runs it a little after 6:00 UTC
Can we change the timings at which this query will run or can we modify this query to Trigger: when user acts on a posts or when user edits or creates a post?
If possible kindly change the query as I am new to this discourse system if you can help me out once then from next time I can build my own custom badge.