Ecto table join

Question

The Ecto documentation shows how to join 2 tables (A<-B) based on the association defined in the schema. I'd like to expand it by adding another table (B <- C)

https://hexdocs.pm/ecto/Ecto.html#assoc/2

But I get this error. How do I reflect the fact that :comments belong to :posts in this query?

Repo.all from u in App.User,                                                                                                                                                                
 join: p in assoc(u, :posts),                                                                                                                                                                  
 join: c in assoc(p, :comments),                                                                                                                                                               
 preload: [posts: p],                                                                                                                                                                          
 preload: [comments: c]                                                                                                                                                                        

** (Ecto.QueryError) field `App.User.comments` in preload is not an association in query:                                                                                                         

from u in App.User,                                                                                                                                                                                 
  join: p in App.Post,                                                                                                                                                                              
  on: p.user_id == u.id,                                                                                                                                                                                
  join: c in App.Comment,                                                                                                                                                                          
  on: c.post_id == p.id,                                                                                                                                                                                
  select: u,                                                                                                                                                                                            
  preload: [posts: p, comments: c]                                                                                                                                                                  

    (elixir) lib/enum.ex:651: Enum."-each/2-lists^foreach/1-0-"/2                                                                                                                                       
    (elixir) lib/enum.ex:651: Enum.each/2                                                                                                                                                               
      (ecto) lib/ecto/repo/queryable.ex:119: Ecto.Repo.Queryable.execute/5                                                                                                                              
      (ecto) lib/ecto/repo/queryable.ex:40: Ecto.Repo.Queryable.all/4

amatalai amatalai · Accepted Answer · 2016-09-13T08:03:20

You can preload associations in tree-like structure and then map results as needed.

> subquery = App.Post |> preload(:comments)
#Ecto.Query<from p in App.Post, preload: [:comments]>

> query = App.User |> preload(posts: ^subquery)
#Ecto.Query<from u in App.User, 
preload: [posts: #Ecto.Query<from p in App.Post, preload: [:comments]>]>

> Repo.all(query)
[
  %App.User{...},
  %App.User{...},
  %App.User{
    ...,
    posts: [
      %App.Post{...},
      %App.Post{...},
      %App.Post{
        ...,
        comments: [
          %App.Comment{...},
          %App.Comment{...},
          %App.Comment{...}
        ]
      }
    ]
  }
]

Ecto table join

1 Answers