XF 2.0 Query to make a specific User Group watch specific forum

[Mr Lucky]

Is it possible to run a query to make one specific User Group watch a specific forum or a set of forums?

Thanks

 

[Chris D]

This will probably do it (usual rules about running on a test install, backups etc. apply):

INSERT INTO xf_forum_watch
(user_id, node_id, notify_on, send_alert, send_email)
SELECT user_id, 123, 'thread', 1, 0
FROM xf_user
WHERE FIND_IN_SET(4, secondary_group_ids)

You'd need to run it for each node_id and user group ID. 123 represents the node_id and 4 represents the user group. This will only check the usergroup against the secondary_group_ids field.

 

[Mr Lucky]

Thanks first test didn't work though.

using forum id 33 and secondary user group id 18

INSERT INTO xf_forum_watch
(user_id, node_id, notify_on, send_alert, send_email)
SELECT user_id, 33, 'thread', 1, 0
FROM xf_user
WHERE FIND_IN_SET(18, secondary_group_ids)

Error

MySQL said: Documentation
#1062 - Duplicate entry '4650-33' for key 'PRIMARY'

 

[Chris D]

Just means that one user is already watching that forum. Try changing INSERT INTO to INSERT IGNORE INTO.

 

[Mr Lucky]

Thanks, on first testing that appears to work!

 

[Mr Lucky]

Thanks again Chris

I've asked something similar here, many thanks if anyone can answer that one also:

https://xenforo.com/community/threads/query-to-force-users-watch-your-own-content.152993/

 

[Case]

@Mr Lucky, does this mean any new users added to this usergroup in future will be watching the forum?

 

[Brogan]

No.

The query would have to be run each time a member is added to the user group.

 

[Eagle]

@Chris D & @Brogan I would like to ask a question about watched threads and forums. Is there maybe a query I can run to stop watching threads and forums for banned users? Otherwise, is there no other way to do this, out of Admin Control Panel?

 

[Mr Lucky]

@Chris D & @Brogan I would like to ask a question about watched threads and forums. Is there maybe a query I can run to stop watching threads and forums for banned users? Otherwise, is there no other way to do this, out of Admin Control Panel?

I can't answer that, but I'm sure they should not be getting notications anyway. I vaguely remember a thread about this a few years ago...

 

[Ozzy47]

Banned users do not receive email notification for watched threads.

 

[Eagle]

@Mr Lucky & @ozzy47 Yeah you are both right but it doesn't matter. Let's say you have 10K banned user. So what will happen then? It means storage of a lot of data in a database. This is what I meant.

 

[Kirby]

Well, even 10K users with 100 threads each would be < ~ 30 MB.
That's hardly what I would call a lot of data.

 

[Eagle]

Well — thank you for your answer, which I think was important and valuable. But you know, this is the matter of tastes and colours.

Anyway, I found a solution but I didn't try it yet.

SELECT * FROM xf_thread_watch
LEFT JOIN xf_user_ban ban ON (ban.user_id = xf_thread_watch.user_id)
WHERE ban.user_id;

This will show you the result of banned users who are watching threads.

SELECT * FROM xf_forum_watch
LEFT JOIN xf_user_ban ban ON (ban.user_id = xf_forum_watch.user_id)
WHERE ban.user_id;

And this will show you the result of banned users who are watching forums.

However, you are free to do anything you like. So this is often a matter of taste as I said.

Greetings!

 

[Mr Lucky]

Just means that one user is already watching that forum. Try changing INSERT INTO to INSERT IGNORE INTO.

Strange, I just reran this query as there are users who registered since the last timeand am getting the duplicate entry error even with INSERT IGNORE INTO. instead of INSERT INTO