Nested preload in Ecto

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 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


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


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:})
post2 = Repo.insert!(%Post{title: "Second post", user_id:})
draft1 = Repo.insert!(%Draft{title: "First draft", user_id:})
draft2 = Repo.insert!(%Draft{title: "Second draft", user_id:})
like1 = Repo.insert!(%Like{user_id:, post_id:})
like2 = Repo.insert!(%Like{user_id:, post_id:})
like3 = Repo.insert!(%Like{user_id:, post_id:})
like4 = Repo.insert!(%Like{user_id:, post_id:})
comment1 = Repo.insert!(%Comment{text: "asdf", user_id:, post_id:})
comment2 = Repo.insert!(%Comment{text: "asdf", user_id:, post_id:})
comment3 = Repo.insert!(%Comment{text: "asdf", user_id:, post_id:})
comment4 = Repo.insert!(%Comment{text: "asdf", user_id:, post_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, 
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, 

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

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.


5 thoughts on “Nested preload in Ecto

  1. I completely agree, the examples in the documentation are not as clear as they could be. Atoms and tuples might be a little more wordy, but I think they’re easier to read than having to mentally keep track of whether the colon goes on the left or on the right.

  2. The best and only explanation about preload nested association in ecto I ever seen. I spend many hours wasting my time trying understand this kind of preload, before I foud you blog.
    Thank You, Thank You, Thank You

  3. i just started working with phoenix and elixir. I found this article really useful, thanks for sharing it.

    comment = HelloWorld.Repo.get(HelloWorld.Comment, 1)
    #Ecto.Association.NotLoaded, I did not understand why it says association :task is not loaded, what can i do to make it load? FYI, Task has_many :comments, Comment belongs_to :task

    I used preload() to make it happen.
    HelloWorld.Repo.preload(comment, :task)

    1. Ecto doesn’t automatically load associations, because they can have association themselves and soon in single query you could get entire database.
      It is also wasteful to do it on demand, because this would make many small queries to the database.
      We need to use preload to achieve that. If you first get the comment and then use preload, you are still making two queries to the database. You might want to try using joins and then preload to make it in one query. See second example in the box here:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s