Holistics-postgres public.tryber has acknowledged provider materialized view
DISPLAYTITLE:Holistics-postgres public.tryber_has_acknowledged_provider materialized view
Query
with
tryber_rows as (
select
id,
wp_user_id
from
wp_appq_evd_profile
where
wp_user_id not in (select distinct wp_user_id from wp_appq_user_to_customer)
),
acknowledged_providers_data as (
select
wp_appq_custom_user_field_data.profile_id,
wp_appq_custom_user_field_data.last_update,
wp_appq_custom_user_field_groups.name as provider_group,
wp_appq_custom_user_field.name as provider_type,
wp_appq_custom_user_field_extras.name as provider_name
from
wp_appq_custom_user_field_data
left join wp_appq_custom_user_field_extras on wp_appq_custom_user_field_extras.id::text = wp_appq_custom_user_field_data.value
left join wp_appq_custom_user_field on wp_appq_custom_user_field_extras.custom_user_field_id = wp_appq_custom_user_field.id
left join wp_appq_custom_user_field_groups on wp_appq_custom_user_field.custom_user_field_group_id = wp_appq_custom_user_field_groups.id
where
wp_appq_custom_user_field_groups.id in (1, 2, 4, 5, 9) -- are 'providers'
and wp_appq_custom_user_field_extras.name is not null -- are 'acknowledged'
)
select
t.id as tester_id,
a.provider_group,
a.provider_type,
a.provider_name,
a.last_update
from
tryber_rows as t
left join acknowledged_providers_data a on t.id = a.profile_id
Keys and Indexes
CREATE INDEX tryberhasacknowledgedprovider_index_testerid ON public.tryber_has_acknowledged_provider (tester_id);
CREATE INDEX tryberhasacknowledgedprovider_index_providertype ON public.tryber_has_acknowledged_provider (provider_type);
CREATE INDEX tryberhasacknowledgedprovider_index_providername ON public.tryber_has_acknowledged_provider (provider_name);
CREATE INDEX tryberhasacknowledgedprovider_index_providergroup ON public.tryber_has_acknowledged_provider (provider_group);