Elixir Phoenix - preload association with self

Question

I have the following in my Comment model (Phoenix 1.2):

schema "comments" do
   belongs_to :parent, Test.Comment
   belongs_to :user, Test.User
   belongs_to :post, Test.Post
   has_many :children, Test.Comment, foreign_key: :parent_id
end

During migrations I had added:

create table(:comments) do
   add :parent_id, :integer
   add :user_id, references(:users, on_delete: :delete_all)
   add :post_id, references(:posts, on_delete: :delete_all)
end

I wanted to show the blog post, as well the comments and the replies on the comments (nested comments like reddit) on the post show page.

The comments and the nested comments are getting created correctly, but I am not able to resolve displaying the nested comments with the 'user' preloaded on the post/show page.

So in the post_controller, show function I have this:

post = Post
|> Repo.get(id)
|> Repo.preload(comments: from(c in Comment, order_by: [desc: c.inserted_at]), 
   comments: :user, comments: :parent, comments: :children)

In the _comment.html.eex, the following line I placed throws the error #Ecto.Association.NotLoaded<association :user is not loaded>:

User: <%= @comment.user.username %>

Any help with this will be appreciated.

Update 1:

Output of running post = Post |> Repo.get(48) |> Repo.preload(comments: from(c in Comment, order_by: [desc: c.votes_up]), comments: :user, comments: :parent, comments: :children) as a sample post with comments and reply to comments.

%Test.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
category: "new",
comments: [%Test.Comment{__meta__: #Ecto.Schema.Metadata<:loaded, 
"comments">,
body: "hello there", children: [], id: 69,
inserted_at: ~N[2017-07-28 21:52:49.636919],
parent: nil, parent_id: nil,
post: #Ecto.Association.NotLoaded<association :post is not loaded>,
post_id: 48,
updated_at: ~N[2017-07-28 21:52:49.636933],
user: %Test.User{username: "dude",
comments: #Ecto.Association.NotLoaded<association :comments is not 
loaded>,
...},
user_id: 11},
%Test.Comment{__meta__: #Ecto.Schema.Metadata<:loaded, "comments">,
   body: "working there?",
   children: [%Test.Comment{__meta__: #Ecto.Schema.Metadata<:loaded, 
   "comments">,
   body: "real child reply should be seen in the show post page",
   children: #Ecto.Association.NotLoaded<association :children is not 
   loaded>,
   id: 85, inserted_at: ~N[2017-08-03 21:52:37.116894],
   parent: #Ecto.Association.NotLoaded<association :parent is not 
   loaded>,
   parent_id: 70,
   post: #Ecto.Association.NotLoaded<association :post is not loaded>,
   post_id: 48,
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 5}], 
   user: %Test.User{username: "dude", ...

Update 2:

In the post/show template this is what I have to display the comments:

<%= for comment <- @post.comments do %>
  <%= render "_comment.html", comment: comment, conn: @conn, post: @post %>
<% end %>

And then in the _comment.html partial I do the following to display the parent comment and its nested children comments:

<p>User: <%= @comment.user.username %></p>
<p>@comment.body</p>

<% unless Enum.empty?(@comment.children) do %>
  <%= for child_comment <- @comment.children do %>
    <ul class="nested_comment">
      <%= render "_comment.html", comment: child_comment, conn: @conn, post: @post %>
    </ul>
  <% end %>
<% end %>

Answer 1

From docs

Repo.preload preloads all associations on the given struct or structs.

This is similar to Ecto.Query.preload/3 except it allows you to preload structs after they have been fetched from the database.

In case the association was already loaded, preload won’t attempt to reload it.

Your better option is to use Query.preload since you are doing anyways everything in same pipe.

post = Repo.first(from p in Post,
         join: c in assoc(p, :comments),
         join: u in assoc(c, :user),
         where p.id = ^id,
         preload: [comments: {c, user: u}])

Or you can pass option :force in Repo.preload function but you need to also say tahat you need to preload user for comments association.

EDIT

Example with recursion.

Here is your Post module and struct

defmodule Post do
  schema "posts" do
    field :post_text, :string
    has_many :comments, Comment, foreign_key: :post_id
  end
  
  
  @doc """
    Recursively loads children into the given struct until it hits []
  """
  def load_comments(model), do: load_comments(model, 10)
  
  def load_comments(_, limit) when limit < 0, do: raise "Recursion limit reached"
  
  def load_comments(%Post{comments: %Ecto.Association.NotLoaded{}} = model, limit) do
    model 
        |> Repo.preload(:comments) # maybe include a custom query here to preserve some order
        |> Map.update!(model, :comments, fn(list) -> 
            Enum.map(list, fn(c) -> c |> Comment.load_parents(limit - 1) |> Comment.load_children(limit-1) end)
           end)
  end
end

Here is your Comment module and struct.

defmodule Comment do
  schema "comments" do
    belongs_to :parent, Test.Comment
    belongs_to :user, Test.User
    belongs_to :post, Test.Post
    has_many :children, Test.Comment, foreign_key: :parent_id
  end
  
  @doc """
    Recursively loads parents into the given struct until it hits nil
  """
  def load_parents(parent) do
    load_parents(parent, 10)
  end
  
  def load_parents(_, limit) when limit < 0, do: raise "Recursion limit reached"
  
  def load_parents(%Model{parent: nil} = parent, _), do: parent
  
  def load_parents(%Model{parent: %Ecto.Association.NotLoaded{}} = parent, limit) do
    parent = parent |> Repo.preload(:parent)
    Map.update!(parent, :parent, &Model.load_parents(&1, limit - 1))
  end
  
  def load_parents(nil, _), do: nil
  
  @doc """
    Recursively loads children into the given struct until it hits []
  """
  def load_children(model), do: load_children(model, 10)
  
  def load_children(_, limit) when limit < 0, do: raise "Recursion limit reached"
  
  def load_children(%Model{children: %Ecto.Association.NotLoaded{}} = model, limit) do
    model = model |> Repo.preload(:children) # maybe include a custom query here to preserve some order
    Map.update!(model, :children, fn(list) -> 
      Enum.map(list, &Model.load_children(&1, limit - 1))
    end)
  end
end

Then in controller

defmodule PostController do
  def show(id) do
    model = Repo.get(Post, id) 
      |> Post.load_comments
      
    # rendering, etc...
  end
end

Answer 2

With pure Ecto it is a challenge I think for showing recursive nested comments. You may be able to develop a hybrid using fragment as shown in this answer https://stackoverflow.com/a/39400698/8508536

Here is an example query using raw postgres in Elixir simplified from something similar I did:

qry = "
  WITH RECURSIVE posts_r(id, posterid, parentid, parenttype, body, hash, depth) AS (
        SELECT p.id, p.posterid, p.parentid, p.parenttype, body, age, hash, 1
        FROM posts p
        WHERE p.parentid = " <> post_id <> " AND p.parenttype != 'room'
      UNION ALL
        SELECT p.id, p.posterid, p.parentid, p.parenttype, p.body, p.age, p.hash, pr.depth + 1
        FROM posts p, posts_r pr
      WHERE p.parentid = pr.id AND p.parenttype != 'room'
  )
  SELECT psr.id, psr.parentid, psr.parenttype, psr.body, psr.hash, psr.depth, u.name
  FROM posts_r psr LEFT JOIN users u ON psr.posterid = u.id
"

res = Ecto.Adapters.SQL.query!(Repo, qry, [])

cols = Enum.map res.columns, &(String.to_atom(&1))

comments = Enum.map res.rows, fn(row) ->
  struct(Comment, Enum.zip(cols, row))
end

comments