Remove old thread subscriptions

[joeychgo]

is there a query or something that could remove all thread subscriptions of users who haven't been on the forum for xxx amount of time?

[kh99]

Well, I strongly suggest that you have a backup before you try this, because I haven't tested it extensively. But first try this:

SELECT username, FROM_UNIXTIME(lastactivity) FROM user WHERE lastactivity < (UNIX_TIMESTAMP() - (86400 * X))

where you replace the red X with the number of days of inactivity. If this seems to select the right users, then you can do this:

DELETE FROM subscribethread WHERE userid IN (
      SELECT userid FROM user WHERE lastactivity < (UNIX_TIMESTAMP() - (86400 * X))
)

replacing the X again.

[joeychgo]

It "seems" to have worked.

186599 rows deleted. (Query took 14.3054 sec)

My subscriptions are still there... So I "think" it worked.

Thank you...

--------------- Added [DATE]1419328510[/DATE] at [TIME]1419328510[/TIME] ---------------

Is there a way to make this into a cron job so it can run automatically??

[kh99]

Originally Posted by joeychgo

Is there a way to make this into a cron job so it can run automatically??

You could create a plugin using hook location cron_script_cleanup_daily and this code:

$vbulletin->db->query_write("DELETE FROM " . TABLE_PREFIX . "subscribethread 
                                                   WHERE userid IN (
                                                      SELECT userid FROM " . TABLE_PREFIX . "user 
                                                         WHERE lastactivity < (UNIX_TIMESTAMP() - (86400 * X))
)");

remember to change the X again. And you should probably test it by going to the scheduled task manager and pressing "Run Now" for the Daily Cleanup task. If it says "Daily Cleanup Done" with no error messages then you're good.