XF 1.5 Moving away from Mandrill - Blacklist/unsubscribed list managmente

[Breixo]

Hi! I'm not sure if this is the right place to ask or at the PHP/Mysql subforum (please move if necessary).
As most know, Mandrill is changing and I'm no longer want to use it.
The problem: there was an option to automatically unsubscribe from getting emails. This blacklist is on Mandrillapp, not at Xenforo.
So if I turn now to another provider (or just default Xenforo system), those users who asked for subscription will get emails from me again.

I was able to export the blacklist. So now I have around 14k emails of registered users who don't want to get emails from me (this include invalid emails and unsubscribed accounts).

So I looked for a way to remove these emails from getting emails from Xenforo.
This includes:
a) Turn all these users to 0 at receive_admin_email; email_on_conversation; default_watch_state at xf_user_option table
b) Turn all these users to 0 at email_subscribe at xf_thread_watch table.
c) Turn all these users to 0 at send_email at xf_forum_watch table.
d) Don't know if any other columns should be changed or if just with one of them is enough.

I found some queries by @Jake Bunce (here and here):

UPDATE xf_user_option
SET default_watch_state = 'watch_no_email'
WHERE default_watch_state = ''

UPDATE xf_thread_watch
SET email_subscribe = 0;

and I guess

UPDATE xf_forum_watch
SET send_email = 0;

But this is for ALL users, I just want to change it only for the unsubscribed/blacklist.

I know that an option like "WHERE user_id IN (123,456,789)" can be used. But I hope there's a smarter way to unsubscribe them. An automatic way to get user ID based on email account?

I also checked a plugin like this: https://xenforo.com/community/resources/e-mail-address-restrictions.5001/
But probably most of these users just want to avoid getting new emails but still want to participate in the forum.

Thanks for your help!

PS: hope I explained myself correctly... just don't want to send emails to these people who asked for getting no emails.

 

[Jake Bunce]

Example:

UPDATE xf_thread_watch
SET email_subscribe = 0
WHERE user_id IN (
	SELECT user_id
	FROM xf_user
	WHERE email LIKE '%@domain.com'
)

 

[Breixo]

Great, looks that can solve the problem.
Instead of one by one, can I use:
WHERE email LIKE '(name1@domain.com,email2@gmail.com,email3@hotmail.com)'
Maybe up to 50 emails per query?
Thanks Jake!

 

[Jake Bunce]

Great, looks that can solve the problem.
Instead of one by one, can I use:
WHERE email LIKE '(name1@domain.com,email2@gmail.com,email3@hotmail.com)'
Maybe up to 50 emails per query?
Thanks Jake!

If you have a list of specific email addresses then it would be like this:

UPDATE xf_thread_watch
SET email_subscribe = 0
WHERE user_id IN (
	SELECT user_id
	FROM xf_user
	WHERE email IN ('name1@domain.com','email2@gmail.com','email3@hotmail.com')
)

 

[Breixo]

Great, thanks a lot Jake

 

[Breixo]

I think everything worked smoothly:

1. Downloaded blacklist from Mandrillapp
2. Used this formula with LibreOffice to give the correct format: =CONCATENATE("'",A1,"' ,")
   So all emails have this format: 'name1@domain.com','email2@gmail.com','email3@hotmail.com' (ready for queries).
3. Test in devel
   
4. Backup database!
5. Applied this to queries inserting all emails between ():

UPDATE xf_user_option
SET receive_admin_email = 0
WHERE user_id IN (
    SELECT user_id
    FROM xf_user
    WHERE email IN ('name1@domain.com','email2@gmail.com','email3@hotmail.com')
)

UPDATE xf_thread_watch
SET email_subscribe = 0
WHERE user_id IN (
    SELECT user_id
    FROM xf_user
    WHERE email IN ('name1@domain.com','email2@gmail.com','email3@hotmail.com')
)

I just tested it and everything looks normal.
Thanks a lot Jake!