Ecto closure table insert

ecto_closure_table.ex

defmodule CommentPath do
  @moduledoc"""
  Comment [closure table](http://www.slideshare.net/billkarwin/models-for-hierarchical-data).
  """

  @primary_key false
  schema "comment_paths" do
    field :ancestor_id, Ecto.UUID
    field :descendant_id, Ecto.UUID
    field :depth, :integer
  end

  @doc """
  Creates a self-referencing root node for the first `Comment` in a new tree.
  """
  @spec create_root(comment :: %Comment{}) :: {:ok, integer} | {:error, nil}
  def create_root(comment) do
    {count, _} = Repo.insert_all(CommentPath,
                                       [[ancestor_id: comment.id,
                                         descendant_id: comment.id,
                                         depth: 0]])
    case count == 1 do
      true -> {:ok, count}
      false -> {:error, nil}
    end
  end

  @doc """
  Creates a descendant path to node for a `Comment` reply.

  `comment` (`ancestor`) is the comment being replied to.
  `reply` (`descendant`) is the reply.
  """
  def create_leaf(comment, reply) do
    {:ok, comment_uuid} = comment.id |> Ecto.UUID.dump()
    {:ok, reply_uuid} = reply.id |> Ecto.UUID.dump()
    path_query = """
    INSERT INTO comment_paths (ancestor_id, descendant_id, depth)
      SELECT ancestor_id, $2::uuid, depth + 1
      FROM comment_paths
      WHERE descendant_id = $1::uuid
      UNION ALL
      SELECT $2::uuid, $2::uuid, 0
    """
    Ecto.Adapters.SQL.query(Repo, path_query, [comment_uuid, reply_uuid])
  end
end