How to apply SQL array aggregation function in Ecto

Questions / Help
, , , ,
1

Hi everybody,

Edit for TL;DR:
How I can use SQL array_agg aggregate function with an Ecto fragment.
Particularly how I can nest the selects…

Let me start with some little explanations of my problem (because it might be irrelevant in real world use case…)

Say I have Posts and Users and I want to fetch all users and also all posts associated to each user.
The association I have is only defined in the Post schema with a belongs_to :user, User
So the following give me what I want but with the User duplicated in every row:

iex> Repo.all from p in Post,
         join: u in assoc(p, :user),
         select: {u.name, p.title}
[
  {"José", "Post 1"},
  {"José", "Post 2"},
  {"Joe", "Post A"},
  {"Joe", "post B"}
]

Now what I’m wanting is to get the following:

[
  {"José", ["Post 1", "Post 2"]},
  {"Joe", ["Post A", "Post B"]}
]

I can achieve this result using Enum.reduce with a Map.update in Elixir side:

Enum.reduce(map, %{}, fn {k, v}, acc -> Map.update(acc, k, [v], &[v|&1]) end)

All of the above are examples using select and ending with results having basic typed values (like strings here). And in fact name here is to be considered unique or maybe adding the id as a key, but the point here is that I can always achieve what I want in Elixir side.

And I also know that having has_many :post on the User Schema and then using preload could do the trick but only with having the elixir Structs. I mean I didn’t managed to use select as above to just grab the user name and the video title.

Note: I know that this question might seem to mix concerns like domain and views.
In real world scenario, I would have loaded the Structs and then grab according fields within a view, etc…
But I’m asking for learning purpose.

So I wanted to know if it’s somehow possible to achieve that using more elaborated SQL queries…
I tried many naive code combinations without success…
For example, I tried to use group_by: :user but then I got an error because of the lack of aggregating function.
Then I discovered array_agg aggregate function in SQL (and postgres). I guess I need to use it somehow with fragment but I don’t have any clue how to use it (besides simple example I saw in the documentation)?

So does anyone can give me some hints…

3 Likes
2 
from p in Post,
  join: u in assoc(p, :user),
  group_by: u.name,
  select: {u.name, fragment("array_agg(?)", p.title)}

You can clean it a little by using macro:

defmacro array_agg(field) do
  quote do: fragment("array_agg(?)", unquote(field))

from p in Post,
  join: u in assoc(p, :user),
  group_by: u.name,
  select: {u.name, array_agg(p.title)}

Or if you use many of such custom functions then I have created ecto_function which provides helper functions for defining such macros. So the above will be:

defqueryfunc array_agg(field)

from p in Post,
  join: u in assoc(p, :user),
  group_by: u.name,
  select: {u.name, array_agg(p.title)}
15 Likes
3

Thanks for your clear answer. I appreciated how you gradually put things, finishing by presenting your package.

I took a look and it’s definitely a good idea!

1 Like
4

Thank you very much! i was stuck for multiple hours on this :smiley:
You’re awesome!

1 Like