Selecting values from multiple rows to use in group by

Questions / Help
,
1

I’m struggling to produce a valid ecto query and feel like I might be overlooking something simple, would really appreciate someone taking a look. Let’s say I’ve got the following database model/schema and would like to query for bar records and accumulate the type values from all associated foo tables for use in a group by:

bar
---
has_many(:foo)

foo
---
field :type, :string
belongs_to(:bar)

baz
---
belongs_to(:bar)

Example ecto query where id’d like the foo_types value to be a list of :type values from the associated foo table:

 from bar in Bar,
 left_join: baz in assoc(bar, :baz),
 left_join: foo in assoc(bar, :foo),
 select: %{bar_id: bar.id, baz_id: baz.ic, foo_types: foo.type},
 group_by: [bar.id, baz.field, foo.type]

I know this is more of a SQL question than an Ecto question but what would the query look like if it is possible? Presumably some type of subquery would be needed but I’m struggling to find a solution, any insight would be greatly appreciated!

2

If you’re on Postgresql, consider removing foo.type from the group_by and using array_agg to collect those values. https://www.postgresql.org/docs/current/functions-aggregate.html

1 Like
3

Thanks @al2o3cr, exactly what I needed!