50K + Members, query for Members that have any Email notification

[sibomots]

Over 20 years of use, across the member population (50K) some have Email set as a form of notification.  1000's are stale addresses.

With 50K users, I'm not going to manually go through a subset through ACP to flip buttons.

What is the relationship (ERD-wise) in the schema that I can query to "find member names who have email notification enabled"

I need that list (potentially 1000's) so I can square it with SMTP errors for stale addresses and do some bulk-wise setting.

select M.name, M.email
from core_members M, ....
where ....

 

[Jim M]

Thanks for posting!

Unfortunately, this issue is beyond the scope of our technical support. 👩‍💻

Our technical support is happy to help you with the Invision Community platform, but we're unable to help with things like server management, theme questions and modifications.

I've moved this to our Community Support area where other Invision Community owners will see it and help where they can.

[sibomots]

Nevermind, I think I found the linkage:

 

select M.name, M.email
from ibf_core_members M
where exists (select P.notification_key
from ibf_core_notification_preferences P
where P.member_id = M.member_id and
 P.preference = 'email')

I can work it from this.

Edited July 9, 2022 by sibomots

[sibomots]

A little better query so the admin can focus on the `preference_key` to examine later:

select M.name, M.email, PP.notification_key, PP.preference
from core_members M, ibf_core_notification_preferences PP
where  PP.member_id = M.member_id
and exists (select P.notification_key
from core_notification_preferences P
where P.member_id = M.member_id and
 P.preference = 'email')

 

[AlexJ]

On 7/9/2022 at 4:46 PM, sibomots said:

A little better query so the admin can focus on the `preference_key` to examine later:

select M.name, M.email, PP.notification_key, PP.preference
from core_members M, ibf_core_notification_preferences PP
where  PP.member_id = M.member_id
and exists (select P.notification_key
from core_notification_preferences P
where P.member_id = M.member_id and
 P.preference = 'email')

 

 

I was looking for something like this.. Thanks for sharing. 

On 7/9/2022 at 4:22 PM, Jim M said:

Thanks for posting!

Unfortunately, this issue is beyond the scope of our technical support. 👩‍💻

Our technical support is happy to help you with the Invision Community platform, but we're unable to help with things like server management, theme questions and modifications.

I've moved this to our Community Support area where other Invision Community owners will see it and help where they can.

Is their any IPS document  -  which shares DB mapping? 

[Miss_B]

11 minutes ago, AlexJ said:

Is their any IPS document  -  which shares DB mapping? 

Have a look at this link.

 

[Jim M]

20 minutes ago, AlexJ said:

Is their any IPS document  -  which shares DB mapping? 

Sorry, we do not have a schema and relationship documentation.

[AlexJ]

2 hours ago, Jim M said:

Sorry, we do not have a schema and relationship documentation.

It would be nice to have one. I hope in future, you consider it. Thanks.