How do I filter an Ecto association with a sort

Question

I would like to have a has_one filtered by a sort field on a join table that connects my many-to-many associations.

I have two schemas:

schema "thing1" do
  field :name

  many_to_many :thing2, Thing2, join_through: "thing1_thing2"
end

schema "thing2" do
  field :name

  many_to_many :thing1, Thing1, join_through: "thing1_thing2"
end

And a join table that looks like:

schema "thing1_thing2" do
  field thing1_id
  field thing2_id
  field created_date, :utc_datetime
end

I'd like to add a has_one on thing1 that automatically orders by that created_date. Maybe it looks something like this:

schema "thing1" do
  ...
  has_one :oldest_thing2, Thing2, through: [:created_date, :desc]
end

Is something like this possible? I know filtered association are but not sure about what those filters can do.

An additional option could be to have a boolean field on the association that I could filter for. What does that look like?

Answer 1

You can use filtered associations, so you can do something like:

schema "thing1" do
  ...
  has_one :filtered_thing2, Thing2, where: [flag: true]
end

But I don't think ecto has any support for ordered associations as yet. Should you need to have a combination of ordering and filtering on associations, you can write a custom function by virtue of composable queries.

defmodule Thing1 do
  schema "thing1" do
    ..
    has_one :filtered_thing2, Thing2, where: [flag: true]
  end

  def custom(query)
    from c in query,
      order_by: [desc: c.created_at]
  end
end

You can then make use of this and query like:

assoc(thing1, :filtered_thing2) |> Thing1.custom() |> Repo.all()