I need help to get a query with all the emails of users in one of my journals in order to send a summary mail of new issues published.
I need to discard users with the option “Do not send me an email for these types of notifications” in “An issue has been published” section in their “Notifications” tab “Profile” (see screenshot above).
I use OJS 22.214.171.124.
Could you help me?
Please some help with this.
I need to get a mail list from users in my journals with the “An issue has been published.” profile notification ENABLED. I need an SQL query but I don’t know were are those setting stored in database.
Can you help me with any clue?
Thank you in advance.
ping… my last shot. Please help.
This is tricky one to find as the email notification settings are stored in the database according to which notification permissions are forbidden, stored as a number (268435477 in this case). This query will find the email for all registered users who have not opted out of “issue published” notifications. You’ll need to swap the 1 in
context_id = 1 for the
context_id for the journal you are wanting to grab emails for.
from `users` as `u`
where `u`.`user_id` in
from `user_user_groups` as `uug`
left join `user_groups` as `ug` on `ug`.`user_group_id` = `uug`.`user_group_id`
where `ug`.`context_id` = 1
and `u`.`user_id` not in
from `notification_subscription_settings` as `nss`
where `nss`.`setting_name` = 'blocked_emailed_notification'
and `nss`.`setting_value` = 268435477
Hope this helps and let me know if anything is unclear.
Thank you @ewhanson. As you say it is really tricky.
I have a new version of your query.
notification_subscription_settings needs join with the
context_id too because user can adjust their notification setting for every journal in the site. I added to look for the
blocked_notification setting because user can disable all the notifications for new issues (and this includes email notifications). See screenshot above.
I have added a condition to remove disabled users in the
I have added some more tricky joins in order to get the firstname and the lastname from the
user_settings table. This is not very beauty but in my table, different names for the same user and locale, are inserted and I don’t know why those duplicates are present… so I get just one
givenName and one
familyName for every user. I exclude
givenName ‘pkpadmin’ because my user has this given name in one record and later the same with my “Isaac” firstname (I don’t know how ojs deals with these duplicated records).
Finally I filter using the journal path string instead of
If somebody has a better approach, I’ll be glad to read about it.
select distinct u.user_id, gn.givenname as firstname, fn.familyname as lastname, u.email
from journals jrn
join user_groups ug on ug.context_id=jrn.journal_id
join user_user_groups uug on uug.user_group_id=ug.user_group_id
join users u on u.user_id=uug.user_id
left join (select user_id, max(setting_value) givenname
and setting_value <> 'pkpadmin'
group by user_id) gn on gn.user_id=u.user_id
left join (select user_id, max(setting_value) familyname
group by user_id) fn on fn.user_id=u.user_id
where u.user_id not in
from notification_subscription_settings nss
where nss.setting_name in ('blocked_emailed_notification', 'blocked_notification')
and nss.setting_value = 268435477
and nss.context = jrn.journal_id
Thanks for sharing your updated query. Glad it helped you find the info you needed.
This topic was automatically closed after 12 days. New replies are no longer allowed.