How to use array_agg in ecto to return structs

Questions / Help
, , , , ,
1

Hi, help me write this query please.

I have the following association.

Post belongs to User. User has many posts.

User with id 1 has POST_A, POST_B, POST_C

User with id 2 has POST_D, POST_E

I want to get a tuple in the following format

{
1: [
    %Post{
    id: 1,
    name: postA,
    },
    %Post{
    id: 2,
    name: postB,
    },
    %Post{
    id: 3,
    name: postC,
    }
   ],
2: [
    %Post{
    id: 4,
    name: postD,
    },
    %Post{
    id: 5,
    name: postE
    }
   ]
}

To get the above structure, I wrote the query below

Post
    |> join(:inner, [p], u in assoc(p, :user))
    |> group_by([p, u], u.id)
    |> select([p, u], {u.id, fragment("array_agg(?)", p)})
    |> put_query_prefix(schema)

But I get something like this in return

[
              {
                "d4f025b9-1140-45af-abc0-b7c9fc2d9501",
                [
                  {<<70, 215, 150, 84, 4, 88, 72, 22, 132, 43, 88, 110, 26, 158, 196, 25>>, <<212, 240, 37, 185, 17, 64, 69, 175, 171, 192, 183, 201, 252, 45, 149, 1>>, 0, ~D[2022-02-15], ~N[2022-04-12 21:02:51.957306], ~N[2022-04-12 21:02:51.957306]},
                  {<<203, 54, 114, 206, 2, 76, 77, 120, 177, 69, 123, 24, 25, 88, 130, 229>>, <<212, 240, 37, 185, 17, 64, 69, 175, 171, 192, 183, 201, 252, 45, 149, 1>>, 0, ~D[2021-09-23], ~N[2022-04-12 21:02:51.960054], ~N[2022-04-12 21:02:51.960054]},
                  {<<170, 232, 134, 165, 88, 23, 66, 249, 160, 178, 26, 105, 75, 97, 35, 162>>, <<212, 240, 37, 185, 17, 64, 69, 175, 171, 192, 183, 201, 252, 45, 149, 1>>, 0, ~D[2022-01-04], ~N[2022-04-12 21:02:51.960633], ~N[2022-04-12 21:02:51.960633]}
                ]
              }
            ]

Is there any other way to get the structs or key value pairs without using jason_agg?

2

If you don’t need to do this in SQL, it would be easier in Elixir - for instance, retrieve Posts and then use Enum.group_by to partition them by user_id.

3

Thanks @al2o3cr , for Enum.group_by, I tried the following

Repo.all(Post, prefix: schema)
|> Enum.group_by(& &1.user_id)

But this returns protocol not implemented for structs error

4

What protocol? That’s unexpected, because the code you posted looks like it should work…

5

Sorry that worked, I had another Repo.all in my test which caused this error