Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.

How to query list of users unsubscribed from mailing list?

+3 votes
asked Feb 14, 2017 in Q2A Core by teju2friends

2 Answers

+1 vote
answered Feb 16, 2017 by Nip351
selected Feb 16, 2017 by teju2friends
Best answer

 I'm assuming you meant via SQL.  I added three users in addition to admin. 

I unsubscribed one, ran select * from qa_users, then unsubscribed another and checked the query results again and the same for the third user and from what I can tell, records (users) that are unsubscribed will be found by using:

SELECT * FROM `qa_users` WHERE flags = 33

commented Feb 16, 2017 by teju2friends
commented Feb 16, 2017 by pupi1985
This is actually returning users who don't want to receive emails AND are confirmed:

    define('QA_USER_FLAGS_USER_BLOCKED', 2);
    define('QA_USER_FLAGS_SHOW_AVATAR', 4);
    define('QA_USER_FLAGS_NO_MESSAGES', 16);
    define('QA_USER_FLAGS_NO_MAILINGS', 32);
    define('QA_USER_FLAGS_MUST_CONFIRM', 128);
    define('QA_USER_FLAGS_NO_WALL_POSTS', 256);
    define('QA_USER_FLAGS_MUST_APPROVE', 512);

Bear in mind that it is also assuming all other flags off, which means users should not be blocked, they haven't selected an avatar, they haven't selected a gravatar, etc.
+3 votes
answered Feb 16, 2017 by pupi1985

Flags is a bit field. Which means you need to query using the bitwise operators. The bit that controls this behaviour is bit 32 (the specific flag is QA_USER_FLAGS_NO_MAILINGS). If it has a 1 then the user does not receive emails. If it has a 0 the user receives emails.

So to find all these users you need to check the users that have that flag (without caring about the rest) set to 1:

SELECT * FROM qa_users WHERE flags & 32 > 0

commented Feb 17, 2017 by teju2friends
Thanks.....! This helped me a lot.