Hi
I have the following query
select
am.*,
u.email as from_email,
u.display_name as from_name,
u.avatar as from_avatar,
a.name as app_name,
conf.options->'primary_color' as primary_color,
json_agg(v) as visitors
from auto_messages am
left join users u on u.id = am.from_id
left join apps a on a.id = am.app_id
left join configurations conf on a.id = conf.app_id
left join visitors v
on v.app_id = am.app_id
and match_filters(v.id, am.formatted_default_filters, am.formatted_custom_filters)
where
v.email is not null
and
case
when am.window_enabled
then
extract(dow from current_timestamp at time zone COALESCE((v.location->>'timezone'), 'Europe/Paris')) = any (am.window_days_of_week::int[])
and current_time at time zone COALESCE((v.location->>'timezone'), 'Europe/Paris') between am.window_start and am.window_end
else
true
end
and not v.unsubscribed
and am.status = 'live'
and am.channel = 'email'
and NOT EXISTS(
SELECT 1 FROM auto_message_events
WHERE auto_message_id = am.id AND visitor_id = v.id AND event = 'sent'
)
group by am.id, am.title, u.email, u.display_name, u.avatar, a.name, conf.options
If I want to write this in Ecto, I will need to have a
select: %{
..
visitors: fragment("json_agg(?)", v)
}
Somewhere, but you cannot just pass the table reference v like this, as it will complain about that variable not existing. It only works when you access an attribute on the table like v.id, but then I don’t get all the data.
Any workarounds?
