Ecto has_one sort association

Yes, you can just do it the same way as for has_many.

So for a schema like this:

 schema "users" do
    field :email, :string
    field :password, :string, virtual: true, redact: true
    field :hashed_password, :string, redact: true
    field :confirmed_at, :naive_datetime
    has_many :comments, Comment
    has_one :latest_comment, Comment
    timestamps(type: :utc_datetime)
  end

You can achieve it like this:

latest_comments_query = from c in Comments, where: c.approved == true, order_by: [desc: c.inserted_at], limit: 1

Repo.all from u in User, preload: [latest_comment: ^latest_comments_query]

Sorry what I mean was to have both where and order_by together when the association is defined.

Something like:

schema "users" do
    ....

    has_many :comments, Comment
    has_one :latest_comment, Comment, where: [status: :approved], preload_order: [desc: :approved_at]
  end

In has_many exists preload_order option (Ecto.Schema — Ecto v3.14.0) that can be used to sort the related records

That sort of dynamic relationship isn’t how has_one is meant to work. You have to manually control a has_one such as: When you add a new comment, you would unset the current latest_comment and then set the new entry as the latest_comment.

What you’re thinking would take the shape of a virtual: true field, and you’d define a query like above. When you load the struct, you’d then run a separate query for the latest_comment, and populate the virtual field.

Aha, I see. No that doesn’t seem possible. You could use my solution (even if it less clean) or a virtual field like @jerdew mentions.

Hey @lorenzomar the general challenge with this is when you are dealing with a collection of users and you want to get the most recent approved comment for each of them. This is doable with a lateral join, but at least last I looked Ecto won’t do this for you.

What will happen in the case of a has_one with a preload_order is that it will load ALL comments and then get the first one in memory. This is going to be a foot gun when you get more records.

Hi @benwilson512, I’m not sure why would a (theoretical at the moment) preload_order implementation load all the records in memory, since it already has a limit 1 in the select, did I miss something?

I opened a proposal PR for an example implementation, which worked for our use case : feat: add preload_order for has_one by saveman71 · Pull Request #4505 · elixir-ecto/ecto · GitHub

While I agree that if you need this feature, your schema is probably flawed, but as it was basically free to implement I hope to see it land in main.

From what I see in the Ecto debug log output, the standard preload query does not include a limit 1.
My guess: Because it is used both to preload for a single item and to preload for a list of items:

Repo.preload(comment, :latest_comment)
Repo.preload([comment_1, comment_2], :latest_comment)

For this reason, I think this suggestion is broken, as it selects only 1 comment overall, instead of one comment per user:

(Maybe it used to work, but the Ecto internals have since changed?)

Instead of a limit 1 it should be distinct on user_id:

latest_comments_query = from c in Comments, where: c.approved == true, order_by: [desc: c.inserted_at], distinct: c.user_id