I have a query I want to run on a user object. I’m using Select to reduce the throughput from the database. Each user has a many to many relationship with departments. What I want to do is return a list of the department id’s in the select. So far, I have this:

context
  |> join(:left, [u], ud in assoc(u, :user_departments))
  |> select([u, ud], %{
    id: u.id,
    name: u.name,
    email: u.email,
    ...
    departments: ud.department_id
  })

There are a lot more items called in my actual query. Obviously, the above fails because it returns only one id and errors if more than one are returned.

I’ve tried group_by, but unsuccessfully. Has anyone got a good example of this? I can’t find any Googling.

Thanks