Ecto how to query many_to_many with in?

I got classic users -> roles relation, that I model through a connecting table like so:

schema “users” do
field :username, :string
field :last_name, :string
field :first_name, :string
many_to_many :roles, Role, join_through: “user_roles”, on_replace: :delete
timestamps()
end

I would like to fetch all the users in particular role. I do something like this:

role = Repo.one(from r in Role, where: r.name == “participant”)
(from u in User, where: ^role in u.roles, preload: [:roles], select: u) |> Repo.all

This does not work. I get an error stating that

“field roles in where is a virtual field in schema User in query.”

Is there a simple way to achieve this?

Would

Role
|> where(name: "participant")
|> join(:inner, [r], u in User, r.user_id == u.id)
|> select([r, u], u)
|> Repo.all()

work?

Or, the same query but using keyword syntax:

query = from r in Role,
  where: r.name == "participant",
  join: u in User, on: r.user_id == u.id,
  select: u

Repo.all(query)

Role and User are connected through a third table user_roles. That table has no entity defined for it. So your join does not work as roles table does not define user_id field.

Anyway, I did find a simple solution: I added users as many_to_many to Role. This way I can actually preload users for a particular role. Still the fact that I can not do much with query APIs bothers me a bit. For example paging through a list of users would not be possible in my simplified solution.

Then you can use that table instead, or define a schema for it.

Role
|> where(name: "participant")
|> join(:inner, [r], ur in "user_roles", r.id == ur.role_id) # or use a schema for user_roles
|> join(:inner, [r, ur], u in User, ur.user_id == u.id)
|> select([r, ur, u], u)
|> Repo.all()

Still the fact that I can not do much with query APIs bothers me a bit.

You can do quite a bit with it, actually, it’s very flexible. I tried to follow postgres manual while rewriting its examples with ecto and I think I only didn’t find a way to express inheritance.

1 Like

what I meant to say you can not do much with the API without defining schema for the connecting table. I do understand you can a lot of things with Ecto Query APIs.

I do want to avoid extraneous schema definitions as much as possible. I have no good use for UserRoles entity (I do not intend to have additional fields on it, for example) anywhere in my code except for fetching stuff.