Return nested map from Ecto Query

Hi! I have a Project table and I’m trying to get a data structure like this from Ecto:

[
   '2017-06-01' => [%Project{}, %Project{}],
   '2017-06-02' => [%Project{}],
]

Basically I’m trying to group by my projects by it’s inserted_at column. Here’s what I have so far:

query = from p in Project, group_by: [p.id, fragment("date(?)", p.inserted_at)], select: %{fragment("date(?)", p.inserted_at) => p}

Which returns a structure like:

[ %{{2017, 04, 21} => %Project{}}, %{{2017, 04, 21} => %Project{}}]

Is the data structure I’m trying to build possible with Ecto? Or should I just run the result through Enum.group_by/2 function?

You can use the postgres function array_agg to roll up things in an array, but you need to explicitly select the elements you want since ecto doesn’t know how to get p out from the resulting array. Something like:

select: %{fragment("date(?)", p.inserted_at) => fragment("array_agg(?)", p.id)}
3 Likes

Thank you! I’ll try this out! :smile: