Nested preload in Ecto

Follow @snajper47

I am writing this post, because it took me some time to fully understand how to nest preloads in Ecto. Nesting mechanism isn’t actually difficult, but I find using keyword list syntax in the docs really confusing.

Preloads in Ecto is a cool feature that allows programmer to fetch nested data sets from database.

Lets imagine Phoenix application with couple of models.

mix phoenix.new blog
cd blog
mix phoenix.gen.model User users name
mix phoenix.gen.model Post posts title user_id:references:users
mix phoenix.gen.model Draft drafts title user_id:references:users
mix phoenix.gen.model Likes likes post_id:references:posts user_id:references:users
mix phoenix.gen.model Comment comments text user_id:references:users post_id:references:posts
mix ecto.create
mix ecto.migrate

It is a very basic app structure where a user can have many posts and drafts. Posts can have many comments and likes. We need to set the has_many relations manually inside generated models:

schema "users" do
  field :name, :string
  has_many :posts, Blog.Post
  has_many :drafts, Blog.Draft

  timestamps
end

schema "posts" do
  field :title, :string
  belongs_to :user, Blog.User
  has_many :comments, Blog.Comment
  has_many :likes, Blog.Like

  timestamps
end

Lets now seed our database with some data. Key this in in priv/repo/seeds.exs

alias Blog.Repo
alias Blog.User
alias Blog.Post
alias Blog.Draft
alias Blog.Like
alias Blog.Comment
import Ecto.Query

user = Repo.insert!(%User{name: "Kowal"})
post1 = Repo.insert!(%Post{title: "First post", user_id: user.id})
post2 = Repo.insert!(%Post{title: "Second post", user_id: user.id})
draft1 = Repo.insert!(%Draft{title: "First draft", user_id: user.id})
draft2 = Repo.insert!(%Draft{title: "Second draft", user_id: user.id})
like1 = Repo.insert!(%Like{user_id: user.id, post_id: post1.id})
like2 = Repo.insert!(%Like{user_id: user.id, post_id: post2.id})
like3 = Repo.insert!(%Like{user_id: user.id, post_id: post1.id})
like4 = Repo.insert!(%Like{user_id: user.id, post_id: post2.id})
comment1 = Repo.insert!(%Comment{text: "asdf", user_id: user.id, post_id: post1.id})
comment2 = Repo.insert!(%Comment{text: "asdf", user_id: user.id, post_id: post2.id})
comment3 = Repo.insert!(%Comment{text: "asdf", user_id: user.id, post_id: post1.id})
comment4 = Repo.insert!(%Comment{text: "asdf", user_id: user.id, post_id: post2.id})

Then run:

mix run priv/repo/seeds.exs
iex -S mix phoenix.server

We have now one user with two posts and two drafts. Both posts have two likes and two comments. The author doesn’t know the netiquette and likes its own posts. Twice! But lets keep things simple for the example :) You can try following examples in Elixir console, but first lets import and alias couple of things:

alias Blog.Repo
alias Blog.User
import Ecto.Query

We can now query for all users like this:

Repo.all(from u in User)

It returns a list of %User structs where posts and drafts are not loaded:

posts: #Ecto.Association.NotLoaded

If we want user with its posts, we can preload them:

Repo.all(from u in User, preload: :posts)

For simplicity, lets assume that a preload is either:
* an atom :relation_name
* nested list of atoms [:relation_name OR {:relation_name, preload}, ...]

The nested list part may not be fully clear at first. It means that a list item might be either an atom or a tuple {:relation_name, preload} where preload can be an atom or a list itself. The definition is recursive.

So there are three differnt equivalent solutions to preload posts:

Repo.all(from u in User, preload: :posts)
Repo.all(from u in User, preload: [:posts])
Repo.all(from u in USer, preload: [{:posts, []}])

If we want to preload both posts and drafts, the first option is not applicable:

Repo.all(from u in User, preload: [:posts, 
                                   :drafts])
Repo.all(from u in User, preload: [:posts, 
                                   {:drafts, []}])

It means we can preload even very nested structures:

Repo.all(from u in User, preload: [{:posts, 
                                    :comments}, 
                                   :drafts])

Repo.all(from u in User, preload: [{:posts, 
                                    [{:comments,
                                      :user},
                                     :likes]},
                                   :drafts])

Last example loads users with its posts comments and even users who posted the comments!

The downside of this query is that it makes 6 queries to the database. One for each model. The preload queries look like SELECT something FROM models WHERE id IN [list of ids]. After fetching the data Ecto makes sure that fetched entities will land in proper places inside our nested result. If you use joins, Ecto can load nested results in one query, but it uses a little bit different syntax.

Why I was confused by the keyword list syntax? Lets start with simple example.

Repo.all(from u in User, preload: [posts: :likes])

This looks OK, but now if you want to add drafts at the same level as posts what do you do? This solution:

Repo.all(from u in User, preload: [posts: :likes, :drafts])

obviously results in syntax error. Keyword lists only work at the end of argument list, so lets try something else:

Repo.all(from u in User, preload: [:drafts, posts: :likes])

surprisingly this works even though it is equivalent to:

Repo.all(from u in User, preload: [:drafts, [{:posts, :likes}]])

Actually, we can nest the lists as many times as we want to:

Repo.all(from u in User, preload: [[[[[:drafts, [{:posts, :likes}]]]]]])

This is why I wrote “lets assume” what a nested list of atoms is. We can make the structure arbitrary complicated and confusing.

Even though there is an example of keyword list syntax in the docs I don’t really recommend using it. The structure is not keyword list after all, but nested atom list, so I would stick with atoms and tuples.

What do you think about the syntax for preloads? Let me know in comments.

Follow @snajper47