Combine two ecto queries on the same schema into one with subquery or join

I have a Post schema with the virtual field children that I currently am populating by running a second query after first getting my Post.

Initial query to get the Post:

post = Post |> Repo.get(id)

then I run a second query to get its children and update the virtual children field:

children_query =
    from(p in Post,
      where: fragment("hierarchy <@ ?", ^post.hierarchy),
      order_by: p.inserted_at
    )

children = Repo.all(children_query)
post = Map.merge(post, %{children: children})

The hierarchy field is stored as an Ltree in the db and has the type of :string in the schema.

Is there any way that I could combine these queries into one? I tried to work with the Ecto subquery/2 function but haven't been able to grok it.

I tried this but couldn't figure out how to pass the Post (p in this case) into the children subquery without getting the error that the variable p is undefined on the join line.

  def children_query(post) do
    from p in Post,
    where: fragment("hierarchy <@ ?", ^post.hierarchy),
    order_by: v.inserted_at
  end

  def get_post(p_id) do
    from(p in Post, 
    where: p.id == ^p_id,
    join: c in subquery(children_query(p)),
    on: p.id == c.id, # not sure how it would join on
    select: %{p | children: c}
    )
  end

I want to figure this out because it becomes very inefficient when showing the Post index page and having to run an additional children query for every post that is listed.

Solution

I suppose the subquery cannot be parametrized that way because it’s to be executed before the main query. One could move both conditions to be in the main query.

children = 
  from(p in Post,
       join: s in subquery(from p in Post),
       where: p.id == ^p_id and fragment("? <@ ?", s.hierarchy, p.hierarchy),
       select: [s, p])

The above produces a bit redundant result with Post glued to each of its children, but I did not manage to make it better.

Now you only need to split the result.

{post, children} =
  case Repo.all(children) do
    [[_, post] | _] = result -> {post, Enum.map(result, &hd/1)}
    [] -> {Repo.get(Post, p_id), []}
  end

The latter query is needed since when there are no children, the join returns an empty set.