Automatically Apply Badges to Group Members (in Hosted Version)

[apautler (Andrew Pautler)]

I’d like to automatically apply certain badges to users in certain groups. In searching the forum, it appears this may be possible via SQL queries, etc. We are on the hosted plan, so that is not possible. In talking with Discourse support, it appears a solution might be to make an app/script that uses the API to apply the badge to users in a groups. Two questions with this:

1. Has someone done this already and would be willing to share?
2. How would I implement an app/script that uses the API within the hosted forum setup?

[sam (Sam Saffron)]

@apautler

We are on the hosted plan, so that is not possible.

It is possible on hosted plans, just let us know by emailing team@discourse.org and we can enabled them.

[apautler (Andrew Pautler)]

@sam Gotcha. What do you think is the better option though? Is it dangerous to begin making SQL queries from within the hosted platform? Makes me a bit nervous because there are other admins in our account and I worry if they accidentally do something in the currently hidden SQL area, it could seriously mess up our database.

[sam (Sam Saffron)]

If this is a once off I would just make the badges, let us know when you are done and then disable badge SQL.

Should be fine.

I think it makes sense to extend groups to, optionally, automatically grant a badge, so SQL is not involved.

You can also do this via the API but it would require running a regular job on your end.

[Cozdabuch (cosmo)]

Is there a way to paste an example code that we can upload on our site?
Is user1 is added to groupX, award badgeX

Simple, nothing more to it

[scombs (Steve Combs)]

Here is the badge SQL, just replace your_group_name

select user_id, created_at granted_at, NULL post_id
from group_users
where group_id = (
  select g.id from groups g where g.name = 'your_group_name'
)

[Cozdabuch (cosmo)]

This will run via the scheduler correct? It is not triggered be the group add, but just runs daily and awards based on group membership?

[scombs (Steve Combs)]

In the badge settings you need to set the trigger to daily and select the option for revocation daily

[Cozdabuch (cosmo)]

@scombs, I had posted in another thread, and someone responded almost at the same time. The code is slightly different. Do you know what the difference would be?

main difference is: gu. vs g.

Other poster’s code:

SELECT gu.user_id, gu.created_at AS granted_at, null AS post_id
FROM group_users AS gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'InsertTheTeamNameHere' )

is there any practical difference?
I’m a complete novice at this, and I just want to give the site owner the code to copy/paste so he doesn’t have to modify it at all.

btw, the group name we are using is: Confirmed_ST_User

[scombs (Steve Combs)]

Not sure. I saw the other post too.

Here is the howto with the code

Grant a badge for all members of a group

I just created a badge for all the awesome plugin authors. Creating a badge targeting a group is quite easy, just type this into the SQL box: select user_id, created_at granted_at, NULL post_id from group_users where group_id = ( select g.id from groups g where g.name = 'GROUP_NAME_IN_LOWER_HERE' ) I will add a trigger for this at some point so granting is faster, in the mean time just go to /sidekiq/scheduler and kick off the badge job to rush it. See Triggered custom badge queries for mo…

[pfaffman (Jay Pfaffman)]

Should be the same as I explained there.

[Cozdabuch (cosmo)]

@scombs

select user_id, created_at granted_at, NULL post_id
from group_users
where group_id = (
select g.id from groups g where g.name = ‘your_group_name’
)

thanks @scombs @pfaffman!!

[riking (Kane York)]

The code is identical, just some renaming, capitalization, and formatting differences.

[paulrudy]

@sam

SQL에 개입하지 않도록 그룹을 확장하여 옵션으로 배지를 자동으로 부여할 수 있도록 하는 것이 합리적이라고 생각합니다.

SQL 없이 배지를 자동으로 부여하는 옵션을 포함하도록 그룹을 확장할 가능성이 있는지 묻기 위해 이 주제를 다시 꺼내는 것이 괜찮을지 모르겠습니다.

[simon]

여기서 참고사항을 하나 덧붙입니다. Standard 또는 Business 호스팅 플랜을 사용하는 사이트에서는 더 이상 커스텀 배지 SQL을 활성화할 수 없습니다. 커스텀 배지를 만들 수는 있지만, 배지를 수동으로 부여하거나 Discourse API를 통해 부여해야 합니다. API를 통해 배지를 부여하는 방법에 대한 자세한 내용은 다음 링크에서 확인하실 수 있습니다: Grant a custom badge through the API.