Ecto Many-to-Many in Select

Questions / Help
,
1

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

2

Can you post the errors?

I think you can either use a preload in the query which will still be a join but it will collect the returned rows into some parent struct (user in your case, it seems), or use a Repo.preload which would avoid a join but rather make a second request.

How to Query a User with has_many Post in Ecto might be relevant.

3

I get a “field departments in select does not exist in schema” or “field user_departments in select does not exist in schema”, even though the User schema has a has_many association to user_departments and a many_to_many association to departments.

schema "users" do
  field :name,  :string
  ...
  has_many :user_departments, UserDepartment
  many_to_many :departments, Department, join_through: UserDepartment
4

Okay, so I guess the answer is I can’t streamline it with a select, but instead I have to return the whole schema and relationships. That’s disappointing, but at least I can associate it with its SQL counterpart, now.

Many thanks @idi527, for your help.

5

I think you can write an ecto query that won’t require you to return unnecessary data …

1 Like
6

Yes, you can streamline the fields of the association.

try something along these lines (assuming a User and a many_to_many relation to departments):

departments_query = from d in Department, select: d.id

from u In User, 
preload: [departments: ^departments_query]
1 Like