SQL to get all users with reviewers role and all the data associated with them

[Vladislav_Mavrin]

Hi
I am trying to make a sql query to get all the reviwers and data associated with them (Names, email, homepage url, ORCID, reviewers interests)

SELECT
u.user_id,
u.username,
u.email,
COALESCE(givenName.setting_value, ‘’)   AS given_name,
COALESCE(familyName.setting_value, ‘’)  AS family_name,
COALESCE(affiliation.setting_value, ‘’) AS affiliation,
COALESCE(orcid.setting_value, ‘’)       AS orcid,
COALESCE(homepage.setting_value, ‘’)    AS homepage_url,
COALESCE(GROUP_CONCAT(DISTINCT revInterests.setting_value SEPARATOR '; '), ‘’) AS reviewing_interests
FROM users u
JOIN user_user_groups uug
ON uug.user_id = u.user_id
JOIN user_groups ug
ON ug.user_group_id = uug.user_group_id
AND ug.context_id = 1          – journal ID 
AND ug.role_id   = 4096     – role

– Name / Last Name / Affiliation / ORCID 
LEFT JOIN user_settings givenName
ON givenName.user_id = u.user_id
AND givenName.setting_name = ‘givenName’
AND (givenName.locale = ‘ru_RU’ OR givenName.locale IS NULL)

LEFT JOIN user_settings familyName
ON familyName.user_id = u.user_id
AND familyName.setting_name = ‘familyName’
AND (familyName.locale = ‘ru_RU’ OR familyName.locale IS NULL)

LEFT JOIN user_settings affiliation
ON affiliation.user_id = u.user_id
AND affiliation.setting_name = ‘affiliation’
AND (affiliation.locale = ‘ru_RU’ OR affiliation.locale IS NULL)

LEFT JOIN user_settings orcid
ON orcid.user_id = u.user_id
AND orcid.setting_name = ‘orcid’

– Homepage: without locale
LEFT JOIN user_settings homepage
ON homepage.user_id = u.user_id
AND homepage.setting_name = ‘url’

– Reviewer interests from controlled vocab
LEFT JOIN controlled_vocabs cv
ON cv.assoc_id = u.user_id
AND cv.symbolic = ‘interest’           – rev interests
LEFT JOIN controlled_vocab_entries cve
ON cve.controlled_vocab_id = cv.controlled_vocab_id
LEFT JOIN controlled_vocab_entry_settings revInterests
ON revInterests.controlled_vocab_entry_id = cve.controlled_vocab_entry_id
AND revInterests.setting_name = ‘interest’   – rev interests in text
AND (revInterests.locale = ‘ru_RU’ OR revInterests.locale IS NULL)

GROUP BY
u.user_id,
u.username,
u.email,
givenName.setting_value,
familyName.setting_value,
affiliation.setting_value,
orcid.setting_value,
homepage.setting_value;

But I get no homepage url or reviewers interests in the resulting table, cant figure it out how to get this particular data.

OJS 3.3.0.8

[asmecher]

Hi @Vladislav_Mavrin,

The homepage URL is the url column in the users table, not a join on user_settings as you have it.

For the reviewer interests, use the user_interests table:

SELECT u.username, cves.setting_value AS interest
FROM users u
JOIN user_interests ui ON (u.user_id = ui.user_id)
JOIN controlled_vocab_entries cve ON (ui.controlled_vocab_entry_id = cve.controlled_vocab_entry_id)
JOIN controlled_vocab_entry_settings cves ON (cve.controlled_vocab_entry_id = cves.controlled_vocab_entry_id)
WHERE cves.setting_name = 'name';

(This works on OJS 3.5.0, but I’m pretty sure it’s also accurate for 3.3.0.)

Regards,
Alec Smecher
Public Knowledge Project Team