I just want to share the experience of deleting the spam reviewer interests from OJS. A while ago, we had found many spam users in our system, so we had used the merge users function as suggested in this forum to get them removed. But shortly after a user found there are many spam reviewer interests existing in our system. After some investigations, we think those spam reviewer interests were probably created by those deleted spam users. So we constructed some SQLs, select and delete those spam interests. Here is the select sql:
Code: Select all
SELECT controlled_vocab_entry_id FROM controlled_vocab_entry_settings WHERE setting_name='interest' AND controlled_vocab_entry_id NOT IN (SELECT DISTINCT a.controlled_vocab_entry_id FROM controlled_vocab_entry_settings a, user_interests b, users c WHERE a.setting_name='interest' AND b.controlled_vocab_entry_id=a.controlled_vocab_entry_id AND b.user_id=c.user_id)
The controlled_vocab_entry_id selected here are those spam interests. Because the field exists in a few tables, we ran 3 delete queries to delete them from controlled_vocab_entries, controlled_vocab_entry_settings, and user_interests. The first 2 are identical. For deleting from users_interests, we just deleted those user_ids not in users table.