(Obsolete) Set category tracking level defaults historically

Documentazione Self-Hosting
46

It looks like you’re typing %? Don’t :wink:. That’s just supposed to be an indication that you’re in the rails console instead of the docker container. I’ll edit the OP to be clearer.

Edit: OP edited.

3 Mi Piace
47

Thanks. Rookie error on my part :smiley:

1 Mi Piace
48

No worries at all. We all start from the beginning at some point.

Is the guide clearer now?

1 Mi Piace
49

Oh it makes total sense. Thanks

3 Mi Piace
50

Actually… I’m stuck on the query. I need to update category ID 1 to notification level of regular (1).
From rails… I’m typing

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

I get response

<PG::Result:0x000055fadb534428 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>

Via the GUI/forums, it looks like it hasn’t changed.

Need Help Troubleshooting an "Internal Server Error" message When New Topic is posted
51

Going to call in an assist here from @sam, PostgreSQL isn’t my area of expertise.

1 Mi Piace
52

Circling back to see if anyone has ideas on where I’m going wrong with my sql query?

54

The query you tried will only update the tracking level IF the user isn’t already tracking/watching/muting a category. It sounds like you want to remove any users that are watching a category.

If that’s correct, try running this on the rails console. It will find all users that are ‘watching’ the category with id=5, and set them to “regular”:

CategoryUser.where(notification_level: CategoryUser.notification_levels[:watching], category_id: 5).update_all(notification_level: CategoryUser.notification_levels[:regular])

:warning: As always, make sure to take a backup before running anything on the console - the smallest typo can have massive consequences!

6 Mi Piace
55

Thanks David. I must have misunderstood the purpose of the query. I thought it was to update (ie lower the subscription level) for all users UNLESS they explicitly set it themselves vs it being assigned via the setting in admin panel.

2 Mi Piace
56

The more precise definition here is “for all users UNLESS it has already been set”. We don’t have any way of knowing whether a user set it themselves, or it was automatically set by the admin panel default.

2 Mi Piace
57

That makes sense because when I (thought I) saw that, I thought “well that’s impressive that we have that granularity”

58

Not to be a pest, but I’m still struggling with this, getting a syntax error when I run this query…

[4] pry(main)> User.exec_sql("INSERT INTO CategoryUser.where(notification_level: CategoryUser.notification_levels[:watching], category_id: 1).update_all(notification_level: CategoryUser.notification_levels[:regular];")

PG::SyntaxError: ERROR:  syntax error at or near ":"
LINE 1: INSERT INTO CategoryUser.where(notification_level: CategoryU...

Any ideas on what I’m overlooking?

59

Sorry, I should have been clearer there. The command I shared does not need to be run inside “exec_sql”. It can just be run directly on the rails console.

3 Mi Piace
60

Sorry to spam the topic with this more rudimentary stuff. I’m still having difficulties

From the rails console (at least I think it is)

/var/discourse# ./launcher rails c
/var/discourse# CategoryUser.where(notification_level: CategoryUser.notification_levels[:watching], category_id: 1).update_all(notification_level: CategoryUser.notification_levels[:regular])

bash: syntax error near unexpected token `notification_level:’

61

You are missing a:

rails c before running the command. You want to be in Ruby not in Bash.

2 Mi Piace
62

You need to run that inside the container.

./launcher enter app

Before the rails command

2 Mi Piace
63

Thanks Jay. Yeah I figured that out while I was working on it today. The trigger being when I was prompted to install rails :joy:

I got this to work today, checked a few accounts via impersonate and it looks like we’ll no longer be spamming our members with 100emails a day (nor paying to send them)

Thanks all

James

3 Mi Piace
64

Immagino ci vorrà un po’ di tempo per completarsi, giusto?
Poiché l’output del comando è stato immediato, con questo risultato:
#<PG::Result:0x000055e37f159100 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=8428>
Quanto tempo potrebbe volerci per completare? Perché ho controllato un profilo utente e sta ancora osservando quella categoria.

65

Dovrebbe essere quasi istantaneo. Tale output mostra che sono state modificate 8.428 voci. Immagino che si tratti del numero di utenti sul tuo forum? Verifica di aver impostato l’ID categoria e l’ID livello di notifica corretti.

2 Mi Piace
66

Non era quella la query che cercavo :man_facepalming:

Questa ha funzionato:

CategoryUser.where(notification_level: CategoryUser.notification_levels[:watching], category_id: 29).update_all(notification_level: CategoryUser.notification_levels[:regular])

È quella pubblicata da @david sopra

1 Mi Piace