How do I set category tracking level defaults historically


(Sam Saffron) #1

Discourse allows you to specify

default categories watching
default categories tracking
default categories watching
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.

  • Step 0, make sure you have a backup

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

  • Step 1, find the category id
./launcher enter app 
rails c
% Category.find_by(name: "my category name").id
=> 666
  • Step 2, find the desired notification_level
muted: 0 
regular: 1
tracking: 2
watching: 3
watching_first_post: 4
  • Step 3, 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  5, u.id, 4
FROM users u 
LEFT JOIN category_users cu ON cu.category_id = 5 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 via

./launcher enter app 
rails c
% User.exec_sql("INSERT INTO category_users(category_id, user_id, notification_level) SELECT 5, u.id, 4 FROM users u LEFT JOIN category_users cu ON cu.category_id = 5 AND cu.user_id = u.id WHERE cu.user_id IS NULL;")

Watching and tracking implementation for tags and categories
Send emails for Posts in Specific Categories
Is there a way to globally suppress a category from mailing list mode?
Question about muting a category
Muted Default Categories Not Working
Engaging users with email notifications of posts
Have-to-read topics? (or RSVP topics)
Sending notifications is slow, throws exceptions
Digest email not to be sent to new users
Mailing list turned OFF - How to Disable Per Post Email for Existing Users
Can I stop user_posted emails?
Best Practice: Creating local user guides
Best way for staff to send notifications about posts
Apply category notification preferences to existing users
I'd like to send a bi-weekly email to all of my users via my Discourse. is that possible?
(Alex Armstrong) #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?

(Régis Hanol) #3

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


(Alex Armstrong) #4

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


(Régis Hanol) #5

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


(Alex Armstrong) #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.


(Leo McArdle) #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.


(Alex Armstrong) #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

(Mittineague) #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.


(Alex Armstrong) #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.


(Mittineague) #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)


(Alex Armstrong) #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?


(Felix Freiberger) #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.


(Alex Armstrong) #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:


(Felix Freiberger) #15

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


(Alex Armstrong) #16

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


(Robert) #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.


(Alex Armstrong) #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:


(Felix Freiberger) #19

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


(Mittineague) #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