Stuck with a simple group_by/aggregate

Phoenix Forum Questions / Help
,
1

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 "a0.id" 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.

2

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
3

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:

4

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)})
|> Repo.one
|> Enum.map(fn {id, arr} -> 
  {id, Enum.map(arr, fn post -> struct(Post, post) end)}
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