lorenzomar
Ecto has_one sort association
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!!
First Post!
victorbjorklund
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]
Most Liked
jerdew
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.
benwilson512
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.
Last Post!
fteschke
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
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Courses that'll move you from confusion to "Aha, now I get it!"
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"