|DISCLAIMER: USE THE FOLLOWING INFORMATION WITH CAUTION AND AT YOUR OWN RISK.
- The queries are far from optimized and/or may cross many tables and can be very cpu and memory intensive. In other words, incorrect usage can cause your server to run out of resources.
- The queries have been generated by community users and have not been approved/validated by PKP.
- The queries are not tested and are not universal (may fail in different ojs versions, database engines, single-multi-tenant, configurations…) so may not work in your server.
Never run an operation against your DB unless you understand what you are doing.
To facilitate the usage, most queries:
- Are defined as VIEWS so that they can be reused in combination with new queries or called in the future. If you do not want to create such views, remove first line and/or adapt the query.
- Include a final “LIMIT 20” statement, to avoid issues if somebody run them after a brainless cut&paste. When you are clear about the number of rows that will be affected and the impact in your server, remove that limit.
- Views are prefixed by “VIEW_” to clearly identify them between your ojs tables.
1. List all Reviewers
CREATE OR REPLACE VIEW `VIEW_ListAllReviewers` AS
group_concat( DISTINCT(grp.user_group_id) ORDER BY grp.user_group_id separator '|') userGroupIDs,
max(bio.setting_value) as biography,
count(distinct rev.review_id) reviews_count,
count(distinct com.comment_id) comment_count
left outer join user_settings giv on (u.user_id = giv.user_id and giv.setting_name = 'givenName')
left outer join user_settings fam on (u.user_id = fam.user_id and fam.setting_name = 'familyName')
left outer join user_settings orc on (u.user_id = orc.user_id and orc.setting_name = 'orcid')
left outer join user_settings aff on (u.user_id = aff.user_id and aff.setting_name = 'affiliation')
left outer join user_settings bio on (u.user_id = bio.user_id and bio.setting_name = 'biography')
left outer join user_settings sig on (u.user_id = sig.user_id and sig.setting_name = 'signature')
left outer join comments com on (u.user_id = com.user_id)
left outer join review_assignments rev on (u.user_id = rev.reviewer_id)
left outer join user_user_groups grp on (u.user_id = grp.user_id)
2. List all Reviewers with their interests
One row for interest:
CREATE OR REPLACE VIEW `VIEW_ListAllReviewersWithInterest` AS
cve.setting_value as INTEREST
FROM VIEW_ListAllReviewers as u
left outer join user_interests ui on (ui.user_id = u.user_id)
left outer join `controlled_vocab_entry_settings` cve on (ui.controlled_vocab_entry_id = cve.controlled_vocab_entry_id)
Interests grouped and separated by a pipe:
CREATE OR REPLACE VIEW `VIEW_ListAllReviewersWithInterestGrouped` AS
order by `ri`.`INTEREST`
ASC separator '|') AS `INTERESTS`
FROM `VIEW_ListAllReviewersWithInterest` as ri