Forums

Unfortunately no one can be told what FluxBB is - you have to see it for yourself.

You are not logged in.

#1 2020-12-31 12:28:33

oli_v_ier
Member
Registered: 2008-09-21
Posts: 49

How to remove subscriptions to discussions of less active members

Hello,

I would like to remove all subscriptions to discussions or forums of members who have not logged in for more than a year, by launching a request on the database.
My forum is currently sending way too many notification emails.

I know how to select the list of members not logged in for more than a year:

SELECT * FROM `pun_users` WHERE` last_visit` <1577836801

but I don't know enough about sql queries to use the result of this query to filter the subscriptions to remove on the pun_topics_subscriptions and pun_forum_subscriptions tables.

Could someone help me? I haven't found a mod that does this.

Offline

#2 2020-12-31 13:31:52

Visman
Member
From: Siberia
Registered: 2010-07-10
Posts: 1,476
Website

Re: How to remove subscriptions to discussions of less active members

My forum is currently sending way too many notification emails.

Inactive users are not to blame for this. For them, emails are no longer sent:
post.php

				// Get any subscribed users that should be notified (banned users are excluded)
				$result = $db->query('SELECT u.id, u.email, u.notify_with_post, u.language FROM '.$db->prefix.'users AS u INNER JOIN '.$db->prefix.'topic_subscriptions AS s ON u.id=s.user_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id='.$cur_posting['id'].' AND fp.group_id=u.group_id) LEFT JOIN '.$db->prefix.'online AS o ON u.id=o.user_id LEFT JOIN '.$db->prefix.'bans AS b ON u.username=b.username WHERE b.username IS NULL AND COALESCE(o.logged, u.last_visit)>'.$previous_post_time.' AND (fp.read_forum IS NULL OR fp.read_forum=1) AND s.topic_id='.$tid.' AND u.id!='.$pun_user['id']) or error('Unable to fetch subscription info', __FILE__, __LINE__, $db->error());

important condition

COALESCE(o.logged, u.last_visit)>'.$previous_post_time.'

P.S.

SELECT * FROM `pun_users` WHERE` last_visit` <1577836801

--->

DELETE 
FROM `pun_topic_subscriptions` 
WHERE user_id IN (SELECT id FROM `pun_users` WHERE `last_visit` < 1577836801)

Last edited by Visman (2020-12-31 13:33:05)

Offline

#3 2021-01-03 22:03:17

oli_v_ier
Member
Registered: 2008-09-21
Posts: 49

Re: How to remove subscriptions to discussions of less active members

Thank you !

Offline

Board footer

Powered by FluxBB