SQL Query - Edit Topic and Forum Subscribers

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
User avatar
profKroy
Registered User
Posts: 183
Joined: Sun Feb 14, 2016 2:39 pm

SQL Query - Edit Topic and Forum Subscribers

Post by profKroy »

Does anyone offhand have or know of a SQL query that will unsubscribe a group of users from all the topics in a forum and from all the subforums in the forum as well. I'm still on 3.2.7 at the moment, fyi.

Context. I teach courses using phpBB. A forum is a course. The subforums represent each week in the semester and contain topics. Students subscribe to subforums and topics for obvious reasons, as we move through the weeks. At the end of the semester I move students into a new group with more limited permissions. However, I need to prevent email notifications from being sent after the course is complete. Thus I need to unsubscribe them somehow.

Thanks for the insight.
Last edited by profKroy on Wed Jan 06, 2021 6:07 pm, edited 1 time in total.
Are you a skilled extension developer? I need an extension developed right now. Please PM me if interested.
User avatar
warmweer
Jr. Extension Validator
Posts: 6198
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium

Re: Looking for a SQL Query

Post by warmweer »

You could change their permissions (can subscribe) but I actually don't know whether this unsubscribes them. (I think not).

Alternatively delete user_records from the topics_track table (or topics_watch). (Ow, there's also forum_track and _watch). On second thought, I wouldn't do that (yet) as I'm not sure about any possible links with other tables (+ you'ld need to explicitly select forum_ and topic_ids)
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52222
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Looking for a SQL Query

Post by Brf »

I would think it should be safe to delete forums_watch and topics_watch for specific forum_id's
The _track tables are for read/unread.
User avatar
EA117
Registered User
Posts: 1970
Joined: Wed Aug 15, 2018 3:23 am
Contact:

Re: Looking for a SQL Query

Post by EA117 »

It probably already came to mind for you, or you already have reasons not to use the approach. But what came to mind for me was making each new semester its own forum, so that anyone who is still subscribed to a previous semester's forum simply never sees any activity. Presumably, based on the current concern you have, I assume you're "emptying out and re-using the same forum" each semester.

So instead of "one forum for ENGL-1202 that gets re-used every semester", you instead have "ENGL-1202 Spring 2021", "ENGL-1202 Fall 2021", etc. You're still free to delete them as soon as you want, or keep them as archive for as long as you want. The key was just that the forum ID for the new incoming class isn't the same forum ID as the outgoing class.
User avatar
warmweer
Jr. Extension Validator
Posts: 6198
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium

Re: Looking for a SQL Query

Post by warmweer »

Brf wrote:
Wed Jan 06, 2021 3:36 pm
The _track tables are for read/unread.
Elementary, my dear Watson warmweer :oops:
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.
User avatar
profKroy
Registered User
Posts: 183
Joined: Sun Feb 14, 2016 2:39 pm

Re: Looking for a SQL Query

Post by profKroy »

EA117 wrote:
Wed Jan 06, 2021 4:51 pm
It probably already came to mind for you, or you already have reasons not to use the approach. But what came to mind for me was making each new semester its own forum, so that anyone who is still subscribed to a previous semester's forum simply never sees any activity. Presumably, based on the current concern you have, I assume you're "emptying out and re-using the same forum" each semester.

So instead of "one forum for ENGL-1202 that gets re-used every semester", you instead have "ENGL-1202 Spring 2021", "ENGL-1202 Fall 2021", etc. You're still free to delete them as soon as you want, or keep them as archive for as long as you want. The key was just that the forum ID for the new incoming class isn't the same forum ID as the outgoing class.
Yes, I had considered that idea, though a main reason that I use phpBB as a teaching platform is to build a knowledge base around my courses. If I could easily duplicate/copy forums and sub forums this could be a possibility. Though, the idea is that all my students are contributing to a community that will support current and future students. Though I recognize as I mentioned that once the course in complete students may not want to keep receiving emails.
Are you a skilled extension developer? I need an extension developed right now. Please PM me if interested.
User avatar
profKroy
Registered User
Posts: 183
Joined: Sun Feb 14, 2016 2:39 pm

Re: SQL Query - Edit Topic and Forum Subscribers

Post by profKroy »

I did discover this extension developed by david63 .

viewtopic.php?t=2562006

From what it looks like I could alter setting for specific users with this extension, but perhaps not for groups. I will pursue this further with the developer. Perhaps it would be an easy modification to make.

Thanks all.
Are you a skilled extension developer? I need an extension developed right now. Please PM me if interested.
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 51807
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: SQL Query - Edit Topic and Forum Subscribers

Post by stevemaury »

To answer the OP's actual question:

Code: Select all

DELETE FROM phpbb_topics_watch WHERE topic_id IN(SELECT topic_id FROM phpbb_topics WHERE (forum_id = X OR forum_id = Y OR forum_id = Z)) AND user_id IN(SELECT user_id FROM phpbb_user_group WHERE (group_id = A OR group_id = B OR group_id = C));

DELETE FROM phpbb_forums_watch WHERE (forum_id = X OR forum_id = Y OR forum_id = Z) AND user_id IN(SELECT user_id FROM phpbb_user_group WHERE (group_id = A OR group_id = B OR group_id = C))
Add additional group_ids and forum_ids as needed.

BACKUP the topics_watch and forum_watch tables before running this.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52222
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Query - Edit Topic and Forum Subscribers

Post by Brf »

You are mixing your ORs and ANDs. You need parenthesis around the ORs, or the AND will only apply to the last condition.
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 51807
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: SQL Query - Edit Topic and Forum Subscribers

Post by stevemaury »

ok, Thanks. I'll fix it.

EDIT - I now think it is correct.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
User avatar
profKroy
Registered User
Posts: 183
Joined: Sun Feb 14, 2016 2:39 pm

Re: SQL Query - Edit Topic and Forum Subscribers

Post by profKroy »

Much appreciated. Thank you.

EDIT: Just to clarify so I understand.

When I run the query it will remove all the subscriptions from all the topics in a sub forum. So if I have 10 subforums, I will not need to run it against each subforum, correct? As I understand the SQL, I just run it once to remove all the subscriptions in all the subforums contained in the designated forum.
Are you a skilled extension developer? I need an extension developed right now. Please PM me if interested.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52222
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Query - Edit Topic and Forum Subscribers

Post by Brf »

profKroy wrote:
Wed Jan 06, 2021 11:21 pm
all the subforums contained in the designated forum
You would need to specify the forum_id of each of the subforums.
stevemaury wrote:
Wed Jan 06, 2021 10:15 pm
I now think it is correct.
It looks better. Personally, I don't like to have more than one "OR" in something like this. I would probably change
(forum_id = x or forum_id = y or forum_id = z) to forum_id in (x, y, z)
User avatar
profKroy
Registered User
Posts: 183
Joined: Sun Feb 14, 2016 2:39 pm

Re: SQL Query - Edit Topic and Forum Subscribers

Post by profKroy »

Works perfectly.
Are you a skilled extension developer? I need an extension developed right now. Please PM me if interested.
User avatar
david63
Registered User
Posts: 18838
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Contact:

Re: SQL Query - Edit Topic and Forum Subscribers

Post by david63 »

You might find this if interest/use - viewtopic.php?f=456&t=2580136
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored
User avatar
profKroy
Registered User
Posts: 183
Joined: Sun Feb 14, 2016 2:39 pm

Re: SQL Query - Edit Topic and Forum Subscribers

Post by profKroy »

david63 wrote:
Thu Jan 14, 2021 8:03 am
You might find this if interest/use - viewtopic.php?f=456&t=2580136
I have in fact found this very useful. I installed it a few weeks ago though I installed an older version for 3.2.7 if I recall correctly. And it is the extension that spawned my request to be able to better manage subscriptions for groups. If I can track students' subscriptions I can better track their engagement my course. Can your extension for 3.3 be developed to allow admins to manage forum/topic subscriptions for groups as well as individual users?
Are you a skilled extension developer? I need an extension developed right now. Please PM me if interested.
Post Reply

Return to “[3.2.x] Support Forum”