Viewing a list of users who are watching a category

Feature
1

We’d like a way to see which users have added a category to their watch list. Ideally this information would be able to be exposed to mods as well, or handled as a new security “type” for the category security settings.

Our use case is that we have mods that we assign to “check-in” with users to make sure that they are subscribing to a particular category and getting updates. Right now there is no real way that we can see to expose that information without going into each user account in admin and checking their preferences.

2 Likes
2

I like statistics and I’m always open to learning different approaches at moderating.

I can see how this feature would be an interesting type of “success metric”, but I can’t envision how this could be applied to moderation tasks.

Please explain further. Thanks.

3

Probably you could

  • write a plugin that does this
  • export the DB and query it
  • write a script that runs on the Discourse server and queries the live DB
4

One of our moderators’ tasks is to ensure signup of members. We’re talking about a few dozen folks here, not hundreds. But it is the moderator’s job to make sure that everyone is participating in the community. So if they could see who was not signed up, they would followup with that user at one of the quarterly in-person meetings we have with our community members.

We have a developer, but he has no experience with the stack that Discourse is built on. Maybe we’ll consider offering a bounty to someone out there if this becomes a major issue in the future.

5

Thanks for getting back. Now I understand. I can’t see it being useful for our forum at the moment but if it ever does I’ll now have an idea on how to go about it.

6

@wesochuck did you manage to solve this?

7

You should be able to use the Data Explorer Plugin to do this.

8

Sure, but that’s like basically run a query to the DB, right? I would be cool to have to info somehow on the admin stats.

9

You run the query in the admin UI under the Plugins area.

10

Since our major goal was to provide this information to moderators, we used the badge system to accomplish this for our purposes.

Here is what we did:

  1. Created a badge that mirrored the category name
  2. Set the badge trigger to run daily
  3. Used this SQL for the badge query
    select user_id, current_timestamp granted_at from category_users
    where notification_level = 3
    and category_id = 15
    union all
    select id, current_timestamp granted_at from users where mailing_list_mode = 't'```
 1.  notification_level = 3 ~~ means they are tracking the category
 1. category_id = 15 ~~ is the category number that we're using the badge to represent
 1. mailing_list_mode = 't' ~~ says if they are in mailing list mode (getting notified for ALL messages into the system) then we will also count them as a subscriber for the category too.

Now a moderator can look at ```/badges``` and determine who is Watching their category.

There are a few tweaks that we should do to improve the badge on the SQL, those include:

1. Someone could be in mailing_list_mode, but they might have MUTED a category, we should take that into consideration
1. notification_level could possibly be switched to >= 2 so it would also capture if they were TRACKING or WATCHING a category.

The release of the data explorer could also be a big help, we just haven't had a chance to play with that much yet.
3 Likes
11

I’m getting this error:

Contract violation:
Query triggers on users, but does not reference the ':user_ids' array
12

OK, if you want it to trigger, then do this:

select user_id, current_timestamp granted_at from category_users
where notification_level = 3
and category_id = 15
and (:backfill OR (user_id IN (:user_ids)))
union all
select id, current_timestamp granted_at from users where mailing_list_mode = 't'
and (:backfill OR (id IN (:user_ids)))
3 Likes
13

I guess fields have changed because now I get:

ERROR:  column "mailing_list_mode" does not exist
LINE 6: ...id, current_timestamp granted_at from users where mailing_li...
14

The table is now: user_options and the column name is mailing_list_mode (which is a boolean field true / false).

4 Likes
15

The updated query working today is:

select user_id, current_timestamp granted_at from category_users
where notification_level = 3
and category_id = 15
and (:backfill OR (user_id IN (:user_ids)))
union all
select user_id, current_timestamp granted_at from user_options where mailing_list_mode = True
and (:backfill OR (user_id IN (:user_ids)))
6 Likes