Sanjibukai

Sanjibukai

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..

Showing Posts 1 to 3

hauleth

hauleth

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)}
16
Post #1
Sanjibukai

Sanjibukai OP

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!

rameramwe

rameramwe

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

— All posts loaded —

Where Next? Top

Trending in Questions Top

Hellowman
Hello everyone! I am new to coding with Elixir/Erlang. And i am looking for some utilities for fuzzing testing like AFL++. Does anyone ...
New
yarrichar
I’m very new to Elixir, so sorry if this is a silly question: I am wondering what happens to the mailbox of a process when the machine i...
New
pragdave
How do I tell Hologram not to escape HTML when substituting into templates? {Textile.render(@product.long_description)} escapes the HTM...
New
mandar1045
I load tested a plain Phoenix chat app backed by Postgres with 100 million messages in it, everything on default settings, to see what br...
New
Fl4m3Ph03n1x
Background When trying to execute mix release on a Windows 11 machine for a Phoenix project I get the following error: * assembling mark...
New
rlipscombe
I’d like to create a file with 600 permissions (rw-------), but there’s a race condition if I create it and then change the permissions. ...
New
saleyn
Is there a way to import record definitions in the iex shell for pretty printing (similar to the rr command in the erl shell)? This woul...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
261 13183 112
New
mudasobwa
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
OndrejValenta
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly) AI dream has many faces, but general...
New
kip
Localize is the next generation localisation library for Elixir. Think of it as ex_cldr version 3.0. The first version will be released ...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
mcass19
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews