How to load data into struct when using ARRAY_AGG fragment

Questions / Help
,
1

I have a self relational table using a parent_id field. I want to get all the first level items, plus their children (only 1 level deep). Here’s my query:

from(
  c in Comment, 
  where: c.post_id == ^post_id and is_nil(c.parent_id), 
  left_join: c2 in Comment, 
  on: c2.parent_id == c.id, 
  group_by: c.id, 
  select: %{c | comments: fragment("ARRAY_AGG(?)", c2)}
) |> Repo.all()

This works, in that it gets the right data, and the parent level data loads into the schema, but the sub comments look like this

[
{<<223, 79, 60, 250, 158, 225, 70, 167, 135, 168, 117, 178, 68, 104, 54, 227>>,
 "This is a second level comment. ", 0, 3,
 <<28, 4, 69, 28, 175, 64, 70, 85, 138, 100, 72, 237, 18, 61, 72, 232>>,
 <<0, 218, 154, 183, 139, 139, 71, 104, 138, 224, 189, 81, 99, 71, 44, 252>>,
 <<193, 80, 129, 199, 190, 162, 79, 40, 130, 246, 134, 162, 195, 159, 7, 73>>,
 ~N[2020-06-24 07:31:39.007612], ~N[2020-06-24 08:24:26.865969], 1},
...
]

So I get:

[
%GoodStruct{
  ...,
  comments: [
    {<<223, 79, 60, 250, ...},
    {<<223, 79, 60, 252, ...},
  ]
},
%GoodStruct{
  ...,
  comments: [
    {<<223, 79, 60, 253, ...},
    {<<223, 79, 60, 254, ...},
  ]
},
]

How can I aggregate the data like this but still load the inner data correctly into structs?

1 Like
2

Can you elaborate on the choice of using array_agg instead of a simple preload?

2 Likes
3

@pejrich not without downsides, but you could use json_agg. It will work as expected, but will return map with string keys, which is a bit annoying.

1 Like