Viewing a list of users who are watching a category

feature
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