Help to construct a SELECT query with email list of users who want to be notified with new issues

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).

2021-05-31_18-32

I use OJS 3.3.0.6.

Could you help me?

1 Like

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?

2021-07-30_13-16

Thank you in advance.

ping… my last shot. Please help.

Hi @isaac.marco,

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.

select `u`.`email` 
from `users` as `u` 
where `u`.`user_id` in 
	(
		select `uug`.`user_id` 
		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 
	(
		select `nss`.`user_id` 
		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.

Regards,

Erik
PKP Team

Thank you @ewhanson. As you say it is really tricky.

I have a new version of your query.

The 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.

2021-08-30_18-42

I have added a condition to remove disabled users in the users table.

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 context_id.

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
      from user_settings
      where setting_name='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
      from user_settings
      where setting_name='familyName'
      group by user_id) fn on fn.user_id=u.user_id
where u.user_id not in
    (
        select nss.user_id
        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
    )
  and u.disabled=0
  and jrn.path='YOUR_JOURNAL_PATH'

Thank you.

1 Like

Hi @isaac.marco,

Thanks for sharing your updated query. Glad it helped you find the info you needed.

Regards,

Erik
PKP Team

This topic was automatically closed after 12 days. New replies are no longer allowed.