Selecting values from multiple rows to use in group by

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:


field :type, :string


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:, baz_id: baz.ic, foo_types: foo.type},
 group_by: [, 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!

If you’re on Postgresql, consider removing foo.type from the group_by and using array_agg to collect those values.

1 Like

Thanks @al2o3cr, exactly what I needed!