(Obsolete) Set category tracking level defaults historically

Documentation Self-Hosting
1

:warning: Following the release of Discourse v2.4.0.beta7 (or if you have this commit), admins now have the option to set the category tracking levels of all existing users when setting the default category tracking levels in their site settings:

  1. Add a category to one of the following site settings, then select the green checkbox button.

    10%20PM
    10%20PM1470×688 62.3 KB

  2. A modal window will pop up with the following options:

    10%20PM
    10%20PM1374×354 29 KB

The old way using the console

Discourse allows you to specify

default categories watching
default categories tracking
default categories muted
default categories watching first post

In site settings.

These setting apply to all new accounts created after the setting is added.

Sometimes, you may decide to make the change historically and apply it to all old accounts. At the moment there is no UI to apply this so you have to crack open a rails console.

If you are a hosted customer contact us and we will sort it out.

If you self-host try the following:

Say you want to set default categories watching first post on all accounts to point to a category.

  1. make sure you have a backup

    Run a backup via the UI, if you mess something up you may want to restore to it.

  2. find the category id

    • Enter the docker container and then launch the rails console.

      ./launcher enter app 
rails c

    • Get the category ID

      Category.find_by(name: "my category name").id

  3. find the desired notification_level

    muted: 0 
regular: 1
tracking: 2
watching: 3
watching_first_post: 4

  4. run a query to backfill changes

    Say I want to set categories watching first post to on a certain category, I look up per step one and find it is 5.

    INSERT INTO category_users(category_id, user_id, notification_level) 
SELECT =CATEGORY_ID=, u.id, =NOTIFICATION=
FROM users u 
LEFT JOIN category_users cu ON cu.category_id = =CATEGORY_ID= AND cu.user_id = u.id 
WHERE cu.user_id IS NULL;

    The above query sets it on all users that do not have an explicit default for the category already set, this means you can re-run it if needed.

    You can run the SQL by

    • Enter the docker container and then launch the rails console.

      ./launcher enter app 
rails c

    • run the query

      % User.exec_sql("INSERT INTO category_users(category_id, user_id, notification_level) SELECT =CATEGORY_ID=, u.id, =NOTIFICATION= FROM users u LEFT JOIN category_users cu ON cu.category_id = =CATEGORY_ID= AND cu.user_id = u.id WHERE cu.user_id IS NULL;")
27 Likes
2

Thanks for this how-to, Sam!

Two issues:

  • There seems to be an issue with Step 1. (Too many quotes?)
  • There’s a ; missing from the end of the User.exec_sql("…") command.

Two questions:

  • Say I’ve previously made everyone “watch” a category with ID 5. How can I remove that setting? (I actually have two categories I need to alter. In one I want to remove the setting altogether and in the other I want to replace it with “watch first”.)
  • How can I apply this to tags instead of categories?
3 Likes
3

I’ve updated @sam’s post with the corrections.

5 Likes
4

The one-liner is still missing the ; at the end.

How_do_I_set_category_tracking_level_defaults_historically_-_howto_-_Discourse_Meta.png708×135 18.4 KB

1 Like
5

I don’t think that’s needed, but I fixed it anyway :wink:

1 Like
6

@zogstrip It could be that I don’t know what I’m doing, but it wasn’t working without the semicolon.

Can I get some help with this?:

I’ve done the obvious thing: running the script to set the notification level to 1, but that doesn’t work because:

My understanding of SQL queries is pretty weak and I wasn’t sure how to counteract this.

7

I don’t think it’s possible, because the category_users table doesn’t have created_at or updated_at columns. If it did, you could delete all the rows which were created/updated at the time you ran the original query.

1 Like
8

I’m afraid I don’t understand your answer. That seems like a weird way of approaching it.

Isn’t there a Ruby or MySQL way of doing (pseudo-code):

for all users
  set notification level of category XYZ to regular
9

That would not revert the changes. For example,

Category XYZ - 15% Watching
Tracking level change
Category XYZ - 100% Watching
Tracking level change
Category XYZ - none Watching

There is no known way t get back to
Category XYZ - 15% Watching

like could be done somewhat accurately with the Group and Topic users tables that do have updated_at fields.

But even then, there are no “previous level” fields available.

1 Like
10

I’m afraid I’m not communicating accurately: I don’t want to “revert” a change. I want to stop all users from watching the “Announcements” category. I guess an alternative would be to create a new category, move all the topics into the new category, delete the old “Announcements” category and rename the new category “Announcements”. It just seems like it should be less painful than that.

11

It is.

If you want to set the tracking level for a category for all members, that is what the first post in this topic details how to do.

except

you will want to not use

WHERE cu.user_id IS NULL

but this instead

WHERE cu.user_id IS NOT NULL

and instead of “4” (watching_first_post) use “1” (regular)

3 Likes
12

Thanks for bearing with me @Mittineague, but I can’t get this to work. The category I’m interested in (“Announcements”) has an ID of 10.

So I ran this:

User.exec_sql("INSERT INTO category_users(category_id, user_id, notification_level) SELECT 10, u.id, 1 FROM users u LEFT JOIN category_users cu ON cu.category_id = 10 AND cu.user_id = u.id WHERE cu.user_id IS NOT NULL;")

It didn’t remove the “Announcements” category from users’ watched list:

For the record, the console returned this on the first try:

=> #<PG::Result:0x0000000aa41000 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=358>

And this on the second:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "idx_category_users_u1"
DETAIL:  Key (user_id, category_id, notification_level)=(3, 10, 1) already exists.
from /home/vagrant/.rvm/gems/ruby-2.3.1/gems/rack-mini-profiler-0.10.1/lib/patches/db/pg.rb:50:in `exec'

Any ideas?

13

If you promise you have a backup and won’t shoot me no matter what happens, you might want to try this:

User.all.each do |user| CategoryUser.batch_set(user, :regular, [10]) end

This should set the tracking level for all users for category 10 to :regular, adapt as needed. It will overwrite any previous settings users have for that category.

1 Like
14

Sorry for taking so long to respond. I had managed to break my local Discourse running some experiments earlier. I set it up again and tried your suggestion, but no joy :frowning:

Edit: BTW, that is what I want to do:

15

Strange, this did work fine for me (although I used it to switch to :muted)…

1 Like
16

I’ll look again, maybe I missed or messed up something.

17

I would change the default tracking level of a category for existing users like this:

  1. start the rails console: rails c once you got into the docker with ./launcher enter app
  2. paste your version of the code below.
# levels: :watching :watching_first_post
level = :watching
category_slug = "news"
category = Category.find_by_slug(category_slug)
group_name = "trust_level_0" # all users
group = group = Group.find_by_name(group_name)

group.users.each do |user|
  watched_categories = CategoryUser.lookup(user, level).pluck(:category_id)
  CategoryUser.set_notification_level_for_category(user, CategoryUser.notification_levels[level], category.id) unless watched_categories.include?(category.id)
end

To change the notification setting for a group inbox, one can use:

GroupUser.where(group_id: group.id).update_all(notification_level: GroupUser.notification_levels[level])

I wonder if you can also work without an explicit ruby loop in the case of categories.

4 Likes
18

Sorry for taking so long to respond:

Indeed. It works for :muted, but not :regular.

I tried @rriemann’s code as well, replacing level = :regular and my category, but again it doesn’t work.

My hunch is that :regular works differently than watching and tracking. Also, now that I’ve set :mute for everyone, How do I unset it? :wink:

1 Like
19

I’d love to know this, too – maybe @sam can help?

1 Like
20

I think the difference might be with using “regular”
I have seen “normal” used in code before.

I was thinking that as long as the integer “1” was used, what descriptor was used would make no difference. Not tested, but I have a feeling that what is wanted here is :normal

But maybe not
https://github.com/discourse/discourse/blob/master/app/models/category_user.rb#L150-L153