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 1419328510 at 1419328510 ---------------

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

[kh99]

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.