Preloading top comments for posts in Ecto

Questions / Help
16

Maybe https://hexdocs.pm/ecto/Ecto.Query.html#join/5-joining-with-fragments would help.

I guess

SELECT comments.* FROM posts
JOIN LATERAL (
  SELECT * FROM comments
  WHERE comments.posts_id = posts.id
  ORDER BY score DESC
  LIMIT 3
) comments ON true
WHERE posts.id IN (1, 2, 3)
ORDER BY posts.id;

might be rewritten with ecto as

defmacrop last_comments(post_id, limit \\ 3) do
  quote do
    fragment(
      "SELECT * FROM comments WHERE comments.post_id = ? ORDER BY score DESC LIMIT ?",
      unquote(post_id),
      unquote(limit)
    )
  end
end

@spec list_some_posts_with_latest_comment :: [Post.t()]
def list_some_posts_with_latest_comment do
  import Ecto.Query

  Post
  |> where([p], p.id in [1, 2, 3])
  |> order_by([:id])
  |> join(:inner_lateral, [p], c in last_comments(p.id))
  |> select([p, c], %{p | last_comments: c})
  |> Repo.all()
end
2 Likes