TIL: Ecto preload with a custom query

Sometimes you may want to pass a custom query to preload function.

There’re department with employees schemata that can be soft-deleted:

# Department schema
defmodule HR.Department do
  use Ecto.Schema

  schema "departments" do
    field :name, :string
    has_many :employees,
      HR.Employee,
      where: [deleted_at: nil]
    timestamps()
  end
end

# Employee schema
defmodule HR.Employee do
  use Ecto.Schema

  schema "employees" do
    field :email, :string
    field :deleted_at, :utc_datetime_usec
    belongs_to :department, HR.Department
    timestamps()
  end
end

Let’s say we want to get department info with all employees, including those that were deleted.

❌️ We cannot just preload it, because has_many field has where: [deleted_at: nil] filtering association ↗.

HR.Department
|> preload([_department], :employees)

❌️ You can pass custom query to preload via joined binding, but it won’t work if you use the assoc macro which respects filtering associations.

HR.Department
|> join(:left, [department], employees in assoc(department, :employees))
|> preload([_department], {employees, :employees})

✅ Instead, you need to build your joined binding from scratch:

HR.Department
|> join(:left, [department], employees in HR.Employee, on: department.id == employee.department_id)
|> preload([_department], {employees, :employees})