Ecto - returning a list of lists

Questions / Help
, ,
1

Hi,

I have two tables in a postgres DB, they a many_to_one relationship. table_one has many table_two. I want a query that returns each id from table_one and a list of ids from table_two:

{
 1: [ 10 , 1 ,  3] , 
 2: [ 15 , 7 ,  8]
}

My query so far:

q = from one in "table_one", 
    join: two in "table_two",
    on: one.id == two.one_id, 
    group_by: [one.id, two.id ],
    select: {one.id, [two.id]}

which gives me:

[
{ 1, [10]},
{ 1, [11]},
{ 1, [3]},
{ 2, [15]},
{ 2, [7]},
{ 2, [8]}
]

Some ecto guidance would be great.

Thanks,

Andrew

2

:wave:

That’s how SQL operates, I think. Even for GROUP BY you always get a table in return unless you use some clever aggregating function like array_agg (in case of postgres):

# not tested
query =
  from one in "table_one",
    join: two in "table_two", on: one.id == two.one_id,
    group_by: one.id,
    select: %{one.id => fragment("array_agg(?)", two.id)}

Or you can “transform” the returned data into the map you want yourself with Enum.group_by in the end.

q = from one in "table_one", 
    join: two in "table_two",
    on: one.id == two.one_id, 
    group_by: [one.id, two.id],
    select: {one.id, two.id}

q
|> Repo.all()
|> Enum.group_by(fn {one_id, _} -> one_id end)
3 Likes
3

Thanks, I couldn’t find a good example of using array_agg in and ecto query. I will check out the enum.group_by

Thanks again.

1 Like
4

Thanks again, all sorted now.

Andrew

1 Like