Ecto query using left join, group by, order by, and count

Tagged count, ecto, group_by, left_join, order_by  Languages elixir

This is an example of an Ecto query that uses a left join, group by, order by, and count to produce a count of associated records for a list:

query = from list in List,
  left_join: subscriber in assoc(list, :subscribers),
  group_by: list.id,
  order_by: [asc: :name],
  select_merge: %{ subscriber_count: count(subscriber.id) }
query |> Repo.all

Remember to add a virtual attribute named subscriber_count:

schema "lists" do
  ...
  field :subscriber_count, :integer, virtual: true

Tested with Ecto 2.2.

Edit  Delete