Array aggregations in Ecto

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:

  1. an attempt to aggregate subscription lists name into array
  2. 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.

Solution: my problem was an end of the working day :slight_smile:

  select: {
    {
      campaign,
      %{
        lists: fragment("ARRAY_AGG(distinct ?)", subscription_lists.name),
        subscribers_count: fragment("COUNT(distinct ?)", campaign_subscribers.id),
      }
    },
  }
3 Likes