As a comment about your current queries, I don’t think preloading with joins is the right call here. If a user makes 3 comments, then that user’s information is sent from postgres 3 times, and Ecto has to deduplicate. Using a normal preload([:user]) without the join will make 2 SQL queries, but they will each be smaller and more efficient.

Yes, and this will be even easier if you do simple preloads:

user_query = from u in User, select: struct(u, [:id, :username])
comment_query = from c in Comment,
  select: struct(c, [:id, :parent_id, :inserted_at, :user_id]),
  order_by: [desc: c.inserted_at]

query = from c in comment_query,
        where: c.post_id == ^post_id,
        where: is_nil(c.parent_id),
        preload: [user: ^user_query, children: {^comment_query, [user: ^user_query}]

Repo.all(query)

You may need to still map over things in order to transform it into the shape you want, but either you’re doing that or the DB is doing that, and it’s generally best if your app does it since the database is the more limited resource.

Thanks for the reply, Ben. That’s interesting that the separate user queries would be faster than the joins. If the users are different for every comment (which will be typical) and I can select for specific user columns in the two joined queries, I’d imagine the joins would be faster, and leave less logic for elixir. Either way…
Sorry, I should’ve been more clear. The focus of my question is really the :children. With the issue being that I can’t choose to encode or select the :children from the Comment struct at the top level but also omit it when nested. It’s frustrating that I’m logging the ecto output and it’s precisely what I need, but there’s no way to get what I want and omit what I don’t without looping over the lists.
I’m wanting something like “encode all values unless they are NotLoaded” … or “ability to select virtual fields” or ??

Oh, I think this is just a misunderstanding about best practices then for JSON encoding. It is NOT considered a best practice to use @derive for doing encodings for an API. Instead you should be using JSON views. This post all the way back from 2015 is still relevant: https://rokkincat.com/blog/2015-03-17-json-views-in-phoenix/

Yea it makes sense that using @derive isn’t a best practice. I’m seeing the limitations first-hand. I looked into Phoenix views and didn’t see anything that would allow me to get what I want without nested loops. Do you know of anything? Can you agree that it would be nice to specify/select the children for the top level and omit it from the nested comments, therefore avoiding the need to loop?

Isn’t that what my example did? I built a query that selected values on comments, and then reused that at both levels without having to specify it twice.

Your code outputs the same ecto structure as mine in that the top-level comments have children preloaded and the nested comments do not . Like I said, the ecto output isn’t the problem. The issue I’m wondering about is converting the ecto structs to maps (and then json) without having to map over the top level and nested lists.

The normal thing is to map over them.

Have you considered creating a separate schema for child comments on the same table which doesn’t define the has_many :children? Then you can move the belongs_to :parent over to it and derive each one separately without mapping to ignore irrelevant keys for each context.

Boom! That is a great idea. Thanks Juan!