Error with ecto query grouping

Questions / Help
, , ,
1

Hi,

I am having difficulty getting a postgres output.

I have the following tables:

  • comments table with id field,
  • posts table with id and connect_id fields, and
  • posts_data table with belongs to associations of posts connect_id and comment_id fields.

I want final output to be like this

[
{connect_id_2, [comment_id_1, comment_id_4, comment_id_7]},
{connect_id_1, [comment_id_3, comment_id_2, comment_id_5]}
]

I wrote the following ecto query

connect_ids = [connect_id_2, connect_id_1]
query = Comment|>put_query_prefix(tenant)

query
    |> join(:inner, [c], pd in PostsData, on: pd.comment_id == c.id)
    |> select([c, pd], {pd.connect_id, c})
    |> where([c, pd], pd.connect_id in ^connect_ids)
    |> Repo.all()

This gives me
[
{connect_id_2, comment_id_1},
{connect_id_2, comment_id_4},
{connect_id_2, comment_id_7},
{connect_id_1, comment_id_3},
{connect_id_1, comment_id_2},
{connect_id_1, comment_id_5}
]

How to resolve this? Thanks for your help in advance.

2

Hey @guccismart27 welcome! You have two options:

  1. Aggregate in SQL:
query
    |> join(:inner, [c], pd in PostsData, on: pd.comment_id == c.id)
    |> where([c, pd], pd.connect_id in ^connect_ids)
    |> group_by([c, pd], pd.connect_id)
    |> select([c, pd], {pd.connect_id, fragment("array_agg(?)", c)})
    |> Repo.all()

You have to drop down to a fragment to use the postgres array_agg aggregator function, but this gets you exactly what you’re asking for.

  1. Aggregate in Elixir
query
    |> join(:inner, [c], pd in PostsData, on: pd.comment_id == c.id)
    |> select([c, pd], {pd.connect_id, c})
    |> where([c, pd], pd.connect_id in ^connect_ids)
    |> Repo.all()
    |> Enum.group_by(fn {connect_id, _} -> connect_id end, fn {_, comment_id} -> comment_id end)
2 Likes