Ash Elixir•2y ago

Manual relationship with recursive CTE

I'm trying to implement this comment/thread (https://discord.com/channels/711271361523351632/1110614023881359430/1111297127897890866) and creating a manual relationship with a recursive CTE (for a tag in a graph structure, get all parent tags). I'm able to get it done with Ecto spitting out grouped map lists. I'm getting a bit stuck figuring out how to get them as an Ash Resource in the end. Is there anything major I'm missing?

  use Ash.Resource.ManualRelationship
  require Ash.Query
  require Ecto.Query

  def load(records, _opts, %{query: _query, actor: _actor, authorize?: _authorize?}) do
    tag_ids = Enum.map(records, & &1.id)

    initial_query =
      Tag
      |> Ecto.Query.select_merge([t], %{original_tag_id: t.id})
      |> Ecto.Query.where([t], t.id in ^tag_ids)

    recursion_query =
      Tag
      |> Ecto.Query.join(:inner, [t], pt in "parent_tags", on: pt.parent_tag_id == t.id)
      |> Ecto.Query.select_merge([t, pt], %{original_tag_id: pt.original_tag_id})

    parent_tags_query = initial_query |> Ecto.Query.union(^recursion_query)
    
    tag_attributes = Tag |> Ash.Resource.Info.attributes() |> Enum.map(& &1.name)
    select_fields = [:original_tag_id | tag_attributes]

    results =
      Ecto.Query.from("parent_tags")
      |> Ecto.Query.select(^select_fields)
      |> Ecto.Query.recursive_ctes(true)
      |> Ecto.Query.with_cte("parent_tags", as: ^parent_tags_query)
      |> Repo.all()

    {:ok,
     results
     # Group by original tag id and drop from final value
     |> Enum.group_by(& &1.original_tag_id, &Map.drop(&1, [:original_tag_id]))}
  end

  use Ash.Resource.ManualRelationship
  require Ash.Query
  require Ecto.Query

  def load(records, _opts, %{query: _query, actor: _actor, authorize?: _authorize?}) do
    tag_ids = Enum.map(records, & &1.id)

    initial_query =
      Tag
      |> Ecto.Query.select_merge([t], %{original_tag_id: t.id})
      |> Ecto.Query.where([t], t.id in ^tag_ids)

    recursion_query =
      Tag
      |> Ecto.Query.join(:inner, [t], pt in "parent_tags", on: pt.parent_tag_id == t.id)
      |> Ecto.Query.select_merge([t, pt], %{original_tag_id: pt.original_tag_id})

    parent_tags_query = initial_query |> Ecto.Query.union(^recursion_query)
    
    tag_attributes = Tag |> Ash.Resource.Info.attributes() |> Enum.map(& &1.name)
    select_fields = [:original_tag_id | tag_attributes]

    results =
      Ecto.Query.from("parent_tags")
      |> Ecto.Query.select(^select_fields)
      |> Ecto.Query.recursive_ctes(true)
      |> Ecto.Query.with_cte("parent_tags", as: ^parent_tags_query)
      |> Repo.all()

    {:ok,
     results
     # Group by original tag id and drop from final value
     |> Enum.group_by(& &1.original_tag_id, &Map.drop(&1, [:original_tag_id]))}
  end

Results look like:

%{
  id: <<115, 49, 103, 214, 71, 206, 68, 225, 173, 194, 151, 80, 189, 122, 121,
    237>>,
  name: "Bikes",
  parent_tag_id: <<35, 126, 25, 129, 171, 216, 76, 141, 185, 212, 147, 171, 74,
    59, 48, 174>>,
}

%{
  id: <<115, 49, 103, 214, 71, 206, 68, 225, 173, 194, 151, 80, 189, 122, 121,
    237>>,
  name: "Bikes",
  parent_tag_id: <<35, 126, 25, 129, 171, 216, 76, 141, 185, 212, 147, 171, 74,
    59, 48, 174>>,
}

8 Replies

jart•2y ago

I’ve done this recently. If you can wait until Monday I’ll dig it up for you.

skanderOP•2y ago

It can definitely wait till then. Have a great weekend! 🙂

jart•2y ago

You too!

jart•2y ago

@skander I had half-written a blog post about it, but never got it finished. I've posted the WIP blog post and the redacted final code. I hope it's helpful. https://gist.github.com/jimsynz/9557a2ad3ec23693f06f8588ba7ed57d

Gist

A half-finished blog post about using recursive CTEs with Ash.

A half-finished blog post about using recursive CTEs with Ash. - ash_recursive_cte_wip.md

skanderOP•2y ago

Thank you so much! It’s still Sunday night here so I’ll be checking this tomorrow. Appreciate it!

jart•2y ago

all good 🙂

skanderOP•2y ago

That worked! Thank you so much. I made a couple modifications so that the query would load all relationships in a batch, then group by the original resource's ID

  @impl true
  def load(records, _opts, %{query: _query, actor: _actor, authorize?: _authorize?}) do
    tag_ids = Enum.map(records, & &1.id)

    tag_attributes = Tag |> Ash.Resource.Info.attributes() |> Enum.map(& &1.name)
    select_fields = [:original_tag_id | tag_attributes]

    results =
      Tag
      |> where([t], t.id in ^tag_ids)
      |> select_merge([t], %{original_tag_id: t.id})
      |> recursive_cte_query(
        "parent_tags",
        Tag,
        :dont_hack
      )
      |> select(^select_fields)
      |> Repo.all()

    {
      :ok,
      # Group by the original tag_id. Final order is highest parent tag first
      results
      |> Enum.reduce(%{}, fn row, acc ->
        {:ok, id} = Ash.Type.cast_input(Ash.Type.UUID, row.original_tag_id)
        Map.put(acc, id, [Map.drop(row, [:original_tag_id]) | Map.get(acc, id, [])])
      end)
    }
  end

  # ...

  def recursive_cte_query(immediate_parents, cte_name, query, _) do
    recursion_query =
      query
      |> join(:inner, [t], pt in ^cte_name, on: t.id == pt.parent_tag_id)
      |> select_merge([t, pt], %{original_tag_id: pt.original_tag_id})

    ancestors_query =
      immediate_parents
      |> union(^recursion_query)

    {cte_name, query}
    |> recursive_ctes(true)
    |> with_cte(^cte_name, as: ^ancestors_query)
  end

  @impl true
  def load(records, _opts, %{query: _query, actor: _actor, authorize?: _authorize?}) do
    tag_ids = Enum.map(records, & &1.id)

    tag_attributes = Tag |> Ash.Resource.Info.attributes() |> Enum.map(& &1.name)
    select_fields = [:original_tag_id | tag_attributes]

    results =
      Tag
      |> where([t], t.id in ^tag_ids)
      |> select_merge([t], %{original_tag_id: t.id})
      |> recursive_cte_query(
        "parent_tags",
        Tag,
        :dont_hack
      )
      |> select(^select_fields)
      |> Repo.all()

    {
      :ok,
      # Group by the original tag_id. Final order is highest parent tag first
      results
      |> Enum.reduce(%{}, fn row, acc ->
        {:ok, id} = Ash.Type.cast_input(Ash.Type.UUID, row.original_tag_id)
        Map.put(acc, id, [Map.drop(row, [:original_tag_id]) | Map.get(acc, id, [])])
      end)
    }
  end

  # ...

  def recursive_cte_query(immediate_parents, cte_name, query, _) do
    recursion_query =
      query
      |> join(:inner, [t], pt in ^cte_name, on: t.id == pt.parent_tag_id)
      |> select_merge([t, pt], %{original_tag_id: pt.original_tag_id})

    ancestors_query =
      immediate_parents
      |> union(^recursion_query)

    {cte_name, query}
    |> recursive_ctes(true)
    |> with_cte(^cte_name, as: ^ancestors_query)
  end

The rest is the same. The key for me here was not knowing you could pass a tuple into select and recursive_ctes Appreciate the help!

jart•2y ago

My pleasure

Gaming

Programming

Manual relationship with recursive CTE

Did you find this page helpful?