title: Ecto - preload only the most recent record
body: <p>I'm trying to write an Ecto query where I am querying for many records and for each record wish to preload only the most recent record from a related table; however, things aren't quite working out as I'd like. Say, for example, I have a data model with many <code>users</code> where each has many <code>comments</code> - the result I'm trying to achieve is a list of all users where each has only their most recent published comment preloaded.</p>
  <p>The closest I've been able to muster is</p>
  <pre><code># assume that my User model has a `has_many(:comments)` relationship defined
  
  defp preload_most_recent_comment(_user_ids) do
    Comment
    |&gt; where([c], c.status == :published)
    |&gt; last(:inserted_at)
    |&gt; Repo.all()
  end
  
  def get_users_with_most_recent_comment() do
    User
    |&gt; preload([comments: ^(&amp;preload_most_recent_comment/1)])
    |&gt; Repo.all()
  end
  </code></pre>
  <p>however, the problem here is that it doesn't preload <em>each</em> user's most recent published comment - it only preloads the single most recent published comment and puts it on the associated user... so the results look something like</p>
  <pre><code>[
    %User{
      id: 1,
      comments: [%{id: 1, status: &quot;published&quot;, inserted_at: ~U[2022-01-01 00:00:00]}]
    },
    %User{
      id: 2,
      comments: []
    }
  ]
  </code></pre>
  <p>even though user <code>2</code> has a published comment. If I remove the <code>last</code> statement, then the preload works essentially the way I'd like it to, albeit that it returns multiple comments for each user, rather than just the most recent.</p>
  <p>What is the appropriate way to adjust this query so that each preload is not only filtered by something like <code>status</code>, but also limited to only the most recent record?</p>