Hi,
I’m running OJS 3.1.2 and I’m looking how to build a query that will get a count for the number of submissions that a user is associated with.
So counting the number of submissions a user has submitted or reviewed or commented on etc.
The reason I ask is we have a large number of spam user’s we want to remove. The problem is I am having trouble identifying those users. Many almost look legitimate. So if I can identify user’s that have submission counts of 0 then even if I accidentally merge (remove) a legitimate user the consequences would be minimal.
You could probably run a few queries based on some sub queries against the various tables where user_ids are used to refer back to the users table. For example:
SELECT distinct users.* from users WHERE users.user_id NOT IN (SELECT user_id from submissions);
Similarly for review_assignments. Bear in mind that there may be users who are just “readers” in the journal, and may have legit reasons for being there and have no author data, so it may also be worth joining across the user_user_groups table for roles, perhaps, if authors and reviewers are what you want. You may not have to worry about queries on submissions because if they haven’t submitted anything or were assigned a review assignment the chance of them commenting on something is minimal. If you use the merge user tool you should be good.
Another way to do this might be to just examine email addresses for users and look at the domains associated with them. Or perhaps just examine the various date fields in the users table. If a user has not logged in since creating their account that might be a flag as well.
Thanks Jason. there is no user_id in the submissions table. Do you know how I can join the user’s table and submissions table? in other words how can I tell if a user has submitted a submission?
Whoops, totally my bad. You’re correct, there is no user_id in the submissions table. Users are mapped to submissions through the stage_assignments table, so you can check there. Apologies for that!