Convert a category to private and add users to a specific group with access

The title might be a bit cryptic, but the context is to convert a category to “private” but have a way to identify all users that participated in it in the past <timeframe> and add them to a group so that it can be set as having access.

Let’s say the group can be created prior to this.

What is currently supported:

  • Create a group
  • Assign permissions for a group to a category
  • Alter the category permissions

What is missing is a way to say “assign these users to a group”, based on their interaction in this category in a specific timeframe.

Maybe there is a query or code that I can execute that can identify those users and assign them to a group?

I’ve used a Data Explorer query to figure out who participated in a topic based on user_actions:

-- [params]
-- int :topic = 3620561


select user_id, 
       sum(action_type) actions,
       sum(case action_type when 5 then 1 else 0 end) replies 
from user_actions ua
     join users u on u.id = ua.user_id
where target_topic_id = :topic
      and moderator is false
      and admin is false
group by ua.user_id

That includes actions such as replies, likes, edits and mentions. I also have a query that includes poll responses. This was for gathering leads, but I expect some of the people who did something besides reply might have a claim on the topic. Defining “participated” might be a useful exercise.

To expand to the entire category, join with topics t on target_topic_id = t.id and add category_id to the where clause. Shouldn’t be hard to define the timeframe based on ua.created_at.

2 Likes

Thank you @jericson, this is exactly what I was looking for.

I am guessing that I could feed the results to a query that adds all users to a group then.
Is there anyone that have had experience doing that? Maybe it’s better to brutally copy-paste the user ids and run a ruby function if there is something available?

That’s usually my preferred solution. But it probably makes more sense to pull a page from the bulk operations examples and apply the where clause on the User object in rails. (Having a test environment for a time like this helps!)

Depending how many there are, you can paste a comma separated list into the Add Users to the group box, if that’s any use?

(There’s also an Automation to add users to a group based on a badge, which you could use this SQL as a base for. That might be a little convoluted if you’re happy with the Rails option though)

1 Like

Just brought the staging environment app for this reason (and also testing what breaks with ember 5 :stuck_out_tongue: )

I’ll dig into it in the next few days, “real job” allowing me the time :smiley:

Just to leave the query completed in case others need it:

select ua.user_id, u.username, sum(action_type) actions, sum(case action_type when 5 then 1 else 0 end) replies from user_actions ua 
join users u on u.id = ua.user_id
join topics t on target_topic_id = t.id
where ua.created_at > 'YYYY-MM-DD'::date and t.category_id = CATEGORY_ID
group by ua.user_id, u.username;

Just replace the YYY-MM-DD with your cutoff date and CATEGORY_ID with your category ID.

The result will show the user_id, username, actions, and replies.

Can you point me to that script @JammyDodger ?

I can’t see it in the link pasted by @jericson

I can extract a list of users (it will be around 250).

1 Like

The options I suggested are available in the UI, though the second one needs the Discourse Automation plugin (it’s a good plugin though :slight_smile:)

1 Like

I am sorry but I don’t understand how that could help me.

I have a list of user IDs (or usernames even) and I need to add them to a group.
I don’t see in the current automations a use case that could help me. There is only adding to group based on badges or custom field but that only means adding a further step (adding a badge to all those users or adding a custom field to users and then adding a value to the specific users I want).

Is there not a bulk_add_user_to_group function or even a add_user_to_group that I can just call in a loop maybe?

Yeah, it would be a little more convoluted doing it that way. But if you have a shortish list you can export the explorer results as a CSV, open up in notepad, and copy&paste them into the Add Users box:

You might be able to do 250 in one go, though I haven’t tried with that many. A handful of batches should cover it though, if not.


It should also be possible through the API using a PUT request to the eg /groups/49/members.json endpoint (you’ll need your own group_id) with a list of your usernames/emails.

1 Like

Worked perfectly, I forgot you could add multiple usernames to the UI :slight_smile:

For others, to export the psql query result simply run:

psql
\o /home/discourse/output.txt
<your query here>
\o
exit

Now in /home/discourse/output.txt you will have the result of the query as it would be printed on screen.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.