Stuck with a simple group_by/aggregate

I’m stuck with a simple group by / aggregation. Let’s say I have a schema, that has a user_id field. And I want something like:

  1 => [%Post{user_id: 1}, %Post{user_id: 1}, ...],
  2 => [%Post{user_id: 2}]

This doesn’t work:

from(p in Post, group_by: p.user_id, select: %{p.user_id => p})
column "" must appear in the GROUP BY clause or be used in an aggregate function

But this does:

from(p in Post, group_by: p.user_id, select: %{p.user_id => fragment("array_agg(?)", p)})

But I get a list of maps, instead of Post structs.

This seems like a fairly straight forward thing, but I can’t figure it out, or find any similar question online.

Try doing it in 2 steps, first select a list of tuples then you load the structs into the result based on the IDs. Just a thought

1 Like

If there is a way to do this within a query then you still might be going against the grain.

The way I see it, SQL’s GROUP BY wants to aggregate records in a merging kind of way, i.e., returning a smaller number of records than without the GROUP BY.

However, the above map wants all the records, but just lumped in a certain way.

In this situation, I think we’re dealing with two different meanings of the term “group by”; SQL’s meaning and Elixir’s meaning, e.g., Enum.group_by/3.

As @evadne suggested, perhaps first retrieve the Post structs and then group them.

If this two-step approach is cause for concern then by all means share it; there might be a better way. :023:

You’re right, I’m mixing the meaning of group_by. I have found a solution that doesn’t require 2 DB queries.

from(p in Post, group_by: p.user_id, select: %{p.user_id => fragment("json_agg(?)", p)})
|> {id, arr} -> 
  {id,, fn post -> struct(Post, post) end)}
|> Enum.into(%{})

Might be faster and cleaner to go with the 2 queries solution. But I guess I’ll have to do some custom aggregation either way.

1 Like