`has_many` field with defined query

Questions / Help
#1

So picture the situations on the code below.

What I would like to do is that has_many(:permissions, through: [:account_roles, :role, :permissions]) only loads the permissions that belongs to an active role (status: enabled) So I want to be able to add some other where clause in the has_many association so developers can do preload:[:permissions] without having to pay attention to the query in between, so it will aways give you the permissions that are active.

Right now has_many constructs a query so I am guessing there is a way to do this because it is pretty much configure it with some query like preload allow me to do it.

But I would love to know what do you think about the use cases (specially the core team of Ecto, because this can be a feature, or probably I shouldn’t be doing that anyway)

P.S: I know I could do the preload with the query filtering the active roles but the idea I am proposing is adding it to the has_many field directly.

schema "accounts" do
        has_many(:account_roles, AccountRole, on_delete: :delete_all)
        has_many(:roles, through: [:account_roles, :role])
        has_many(:permissions, through: [:account_roles, :role, :permissions])
end

schema "account_roles" do
    belongs_to(:role, Role)
    belongs_to(:account, Account)
    timestamps()
  end

schema "roles" do
    field(:status, Status)
    has_many(:account_roles, AccountRole, on_delete: :delete_all)
    has_many(:role_permissions, RolePermission, on_delete: :delete_all)
    has_many(:permissions, through: [:role_permissions, :permission])
  end

schema "role_permissions" do
    belongs_to(:role, Role)
    belongs_to(:permission, Permission)
  end

schema "permissions" do
    field(:name, :string)
    field(:service, :string)
  end
#2

As Ecto.Schema definitions are not only used to retrieve data, but also to store it your proposal would mean that inserting new data would need to be checked against being retrievable by the custom where clause. So to ensure things are correctly stored the item would need to be stored in the db (in a transation), then a query would need to be made checking if the where clause is correctly fulfilled, and if that’s not the case rollback the transaction again and return with errors.

Not having guaranteed the above property people could get into cases, where they Repo.insert data successfully, which on subsequent queries is no longer available.

#3

It was before? Could you point me to the issue tracking of why they remove if then?

I am really not following, when is used to retrieve the data on the insert? Like if I want to add an account, why would permissions with query will affect the inserting?

I am kind of lost

#4

If the has_many(:permissions, …) would automatically resolve to only enabled permissions then the following would break without changing how ecto evaluates successful inserts, because on a subsequent query the added permission would not be found.

%User{…}
|> User.changeset(%{permissions: [%{enabled: false, …}]) #using cast_assoc
|> Repo.insert!