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.
