Hello folks,
I’m trying to escape writing plain SQL and wonder if is it possible to use ARRAY()
aggregation and a kinda of backreference in the query.
Here is what I’m working with
from(
campaign in Campaign,
where: campaign.user_id == ^user_id and campaign.status == ^status,
left_join: campaign_subscription_lists in "campaign_subscription_lists",
on: campaign_subscription_lists.campaign_id == campaign.id,
left_join: subscription_lists in SubscriptionList,
on: subscription_lists.id == campaign_subscription_lists.subscription_list_id,
left_join: subscriber_subscription_lists in "subscriber_subscription_lists",
on: subscriber_subscription_lists.subscription_list_id == subscription_lists.id,
left_join: campaign_subscribers in ^subscribers,
on: campaign_subscribers.id == subscriber_subscription_lists.subscriber_id,
select: {
{
campaign,
%{
subscription_lists: array_agg(select subscription_lists.name from subscription_lists),
subscribers: fragment("(select distinct count(subscribers.email))")
}
},
}
)
Here you can see the two problems actually:
- an attempt to aggregate subscription lists name into array
- an idea to get only unique subscribers by their email
I’m pretty sure that it’s achievable via subqueries but not sure how to do with Ecto.