Ecto has_one sort association

Questions / Help
1

Hi folks,

I have a sample application with User and Comments schemas with a relation 1:m between them.

I would like to add a 1:1 relation on User schema to retrieve last approved comment.

Do you know if there is some sort of preload_order option in has_one as we have in has_many relation?

Thanks in advance!!

2

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]
3

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.11.2) that can be used to sort the related records

4

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.

2 Likes
5

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.

6

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.

2 Likes
7

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.