Filter Ecto associations on join

Questions / Help
1

Hey everybody!

I’m currently struggling with some Ecto queries. I have the following schemas defined which both include some helper functions:

defmodule Account do
  use Ecto.Schema

  schema "accounts" do
    # ...
    has_many :users, User
  end

  def active(query \\ __MODULE__) do
    where(query, state: "active")
  end
end

defmodule User do
  use Ecto.Schema

  schema "users" do
    # ...
    belongs_to :account, Account
  end

  def active(query \\ __MODULE__) do
    where(query, state: "active")
  end
end

I would like to create a query like the following:

User 
|> join(:inner, [u], a in assoc(u, :account), on: Account.active())
|> Repo.all()
|> Repo.preload(:account)

However this does fail with a Account.active() is not a valid query expression error.
I can accomplish what I’m looking for by the following query, however this does not feel right,
especially if the Account.active() function includes more stuff I have to replicate in the query.

User 
|> join(:inner, [u], a in assoc(u, :account), on: a.state == "active")
|> Repo.all()
|> Repo.preload(:account)

Is there a DRYer way to do this?

2

Look at the dynamic macro. It’s what you want to use in those functions.

3

I’m not 100% sure how to solve this with the dynamic macro.

4

Something akin to this:

  def active() do
    dynamic([_, a], a.state == "active")
  end

  active_account = Account.active()
  User 
  |> join(:inner, [u], a in assoc(u, :account), on: ^active_account)
3 Likes