Collaborative list of of queries

Software Support
, ,
1

Hi,

Today we have found that a journal asked us for a list that could not be extracted from the report generator or by any other extraction provided by OJS.

Instead, we were asked for a list of ALL the reviewers of the journal together with their data and their review interests.

To solve the situation, we have made the query directly against the ojs DB and we have delivered the requested data in CSV format.

It occurs to me that the request is not so strange, so I’m starting a wiki-type post to inventory/document queries… either for third parties to use or for future ojs listings to take them into account.

If someone has a similar query, please edit the post or leave it as a comment and I’ll take care of the layout and comment in the main post.

This post is complementary and inspired by this thread:

1 Like
2
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
SELECT 
    u.user_id,
    u.username,
    group_concat( DISTINCT(grp.user_group_id) ORDER BY grp.user_group_id separator '|') userGroupIDs,
    max(giv.setting_value) givenName,
    max(fam.setting_value) familyName,
    max(orc.setting_value) orcid,
    max(aff.setting_value) affiliation,
    u.email,
    u.url,
    u.disabled,
    u.date_registered,
    u.date_validated,
    max(bio.setting_value) as biography,
    max(com.body) comments,
    max(sig.setting_value) signature,
    count(distinct rev.review_id) reviews_count,
    count(distinct com.comment_id) comment_count
FROM
    users u
    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)
GROUP BY
    u.user_id,
    u.username,
    u.email,
    u.url,
    u.disabled,
    u.date_registered
LIMIT 20;

2. List all Reviewers with their interests

One row for interest:

CREATE OR REPLACE VIEW `VIEW_ListAllReviewersWithInterest` AS
SELECT u.user_id,
       u.username, 
       u.givenName, 
       u.familyName, 
       u.orcid, 
       u.affiliation, 
       u.email, 
       u.url, 
       u.disabled, 
       u.date_registered, 
       u.biography, 
       u.reviews_count, 
       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)
LIMIT 20;

Interests grouped and separated by a pipe:

CREATE OR REPLACE VIEW `VIEW_ListAllReviewersWithInterestGrouped` AS
SELECT `user_id`, 
       username, 
       group_concat(
           distinct `ri`.`INTEREST` 
           order by `ri`.`INTEREST` 
           ASC separator '|') AS `INTERESTS`
FROM `VIEW_ListAllReviewersWithInterest` as ri
LIMIT 20;

Enjoy!

2 Likes