How to preload one association for each record?

Question

Say I have the following Ecto models:

ForumCategory, has_many :forums
Forum, belongs_to :forum_category, has_many :forum_topics
ForumTopic, belongs_to :forum

Say then I want to load all entries of ForumCategory with all their associated Forum entries preloaded and the latest ForumTopic for each of these:

ForumCategory -> Forum -> ForumTopic

The preloading forums on forum categories is trivial:

ForumCategory
|> preload(:forums)

Now we also want to preload the latest forum topic, but we only want to preload the single latest topic for each forum, so we don't load in the entire list of forum topics from the database (there will be a lot of them)

forum_topics_query = ForumTopic
                     |> order_by([desc: :inserted_at])
                     |> limit(1)
ForumCategory
|> preload([forums: [forum_topics: ^forum_topics_query]])
|> Repo.all

This seems to work at first glance, but it quickly becomes apparent that this query only ever returns a single forum topic, not one per forum, but a single forum topic in total, which is also clear from the actual SQL query that is executed:

SELECT f0."id", f0."title", f0."body", f0."pinned", f0."forum_replies_count", f0."deleted_at", f0."inserted_at", f0."updated_at", f0."forum_id", f0."user_id", f0."forum_id"
FROM "forum_topics" AS f0 WHERE (f0."forum_id" = ANY($1))
ORDER BY f0."forum_id", f0."inserted_at" DESC
LIMIT 1

So my question is, how do I make Ecto preload not just a single record in total but one per forum that is preloaded on each forum category?

Thomas Dippel Thomas Dippel · Accepted Answer · 2017-02-21T22:48:59

I ended up with this solution that uses a combination of joins and window functions:

defmodule MyApp.ForumTopic do
  def latest_over_forum(per \\ 1) do
    from outer in __MODULE__,
      join: inner in fragment("""
        SELECT *, row_number() OVER (
          PARTITION BY forum_id
          ORDER BY inserted_at DESC
        ) FROM forum_topics
      """),
      where: inner.row_number <= ^per and inner.id == outer.id
  end
end

forum_topics_query = ForumTopic |> ForumTopic.latest_over_forum
ForumCategory
|> preload([forums: [forum_topics: ^forum_topics_query]])
|> Repo.all

How to preload one association for each record?

1 Answers