The discussion revolves around converting a category to private and adding users to a specific group with access. Crius wants to identify users who participated in the category within a specific timeframe and add them to a group.
jericson provides a Data Explorer query to find users who participated in a topic, which can be modified to include the entire category and timeframe. The query can be used to feed the results to a query that adds users to a group.
Crius asks if there’s a way to add users to a group using the query results, and jericson suggests using a Rails function or copying and pasting the user IDs.
JammmyDodger suggests pasting a comma-separated list of user IDs into the “Add Users to Group” box or using an Automation plugin to add users to a group based on a badge.
Crius mentions having a staging environment to test the query and Rails function.
The final query is shared by Crius, which includes the category ID and timeframe.
JammmyDodger suggests using the API to add users to a group or copying and pasting the user IDs into the “Add Users to Group” box. Crius confirms that copying and pasting multiple usernames into the UI works and shares a method for exporting the query results to a text file.
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.
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)
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.
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.
)
)
