Ecto has_many through with condition/filter

Questions / Help
,
1

I’ve got an existing database with an interesting permissions model. The permissions schema are based on a php library called Spatie Permissions. Long story short, I need to join my User’s roles through a model_has_roles table. model_has_roles is generic and allows any model in the system to have roles via a model_type field. For sake of brevity, what I’m trying to do is create an association for this query:

from r in Role,
  inner_join: mhr in "model_has_roles",
  on: mhr.role_id == r.id,
  where: mhr.model_id == ^user.id,
  where: mhr.model_type == "App\\User"

Unfortunately the docs say :where - A filter for the association. See "Filtering associations" below. It does not apply to :through associations.

Am I out of luck? Ultimately I just want track the roles / preload them right on the User model.

2

I’d expect this to work (in the User schema):

has_many :model_has_roles, foreign_key: :model_id, where: [model_type: "App\\User"]
has_many :roles, through: [:model_has_role, :role]

along with a standard belongs_to :role on ModelHasRole

3

This has me so close! I can’t figure out this error though - even though it’s seemingly running the correct query.

iex(3)> Repo.all(from u in User, preload: :roles)

** (ArgumentError) schema ModelHasRole does not have association Role (if you were trying to pass a schema as a query to preload, you have to explicitly convert it to a query by doing `from x in Role` or by calling Ecto.Queryable.to_query/1)
    (elixir 1.11.2) lib/enum.ex:2181: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.5.7) lib/ecto/repo/queryable.ex:235: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.7) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3

Even though I have the association on the ModelHasRole:

belongs_to :role, Role
4

Actually it looks like I had a typo. The issue now is:

iex(9)> Repo.all(from u in User, preload: :roles)
** (ArgumentError) cannot preload through association `model_has_roles` on `User`. Ecto expected the ModelHasRole schema to have at least one primary key field

ModelHasRole indeed has @primary_key false.

5

However this works fine…:

Repo.all(from u in User, preload: [model_has_roles: :role])
6

So I think I figured this out. I was going about it completely wrong. I think this does what I need:

many_to_many :roles, Role,
  join_through: ModelHasRole,
  join_keys: [model_id: :id, role_id: :id],
  join_where: [model_type: "App\\User"]
1 Like