This describes how to make hierarchy SQL-requests with Arel and Common Table Expressions (CTE)

hierarchy.rb

def ancestors
  # Please read descedants comments for the entire method
  return Supplier.none unless persisted?

  # The raw SQL query for getting ancestors could be like this.
  # Please note the differences.
  # sql = <<~SQL
  #   WITH recursive ancestors AS (
  #     SELECT * FROM suppliers_parent_suppliers s
  #     WHERE here s.supplier_id = #{id}
  #     UNION 
  #     SELECT s.supplier_id, s.parent_supplier_id FROM suppliers_parent_suppliers s, ancestors a
  #     WHERE s.supplier_id = a.parent_supplier_id
  #   )
  #   SELECT * FROM ancestors
  # SQL

  ancestors_cte = Arel::Table.new(:ancestors)
  parentships = Arel::Table.new(:suppliers_parent_suppliers)

  anchor_query = parentships
                   .project(parentships[:supplier_id], parentships[:parent_supplier_id])
                   .where(parentships[:supplier_id].eq(id))

  recursive_query = parentships
                      .project(parentships[:supplier_id], parentships[:parent_supplier_id])
                      .from([parentships, ancestors_cte])
                      .where(parentships[:supplier_id].eq(ancestors_cte[:parent_supplier_id]))

  composed_cte = Arel::Nodes::As.new(ancestors_cte, anchor_query.union(recursive_query))

  ancestors_ids = ancestors_cte
                    .project(ancestors_cte[:parent_supplier_id])
                    .with(:recursive, composed_cte)

  Supplier.distinct.where(Supplier.arel_table[:id].in(ancestors_ids))
end

def descedants
  # Th query requires a model to have an id, so it has to bepersisted 
  return Supplier.none unless persisted?

  # This is a simplest possible recursive CTE query, expressed with AREL
  # Please read these two articles before digging into this code:
  #   - Recursive CTE in SQL: https://mariadb.com/kb/en/recursive-common-table-expressions-overview/
  #   - Writing CTE in Arel: https://www.scimedsolutions.com/blog/arel-part-ii-common-table-expressions
  # When you are familiar with the technology, proceed.

  # First, the original SQL request could look like this:
  # sql = <<~SQL
  #   WITH RECURSIVE descedants AS (
  #     SELECT * FROM suppliers_parent_suppliers s
  #     WHERE s.parent_supplier_id = #{id}
  #     UNION 
  #     SELECT s.supplier_id, s.parent_supplier_id FROM suppliers_parent_suppliers s, descedants d
  #     WHERE s.parent_supplier_id = d.supplier_id
  #   )
  #   SELECT * FROM descedants
  # SQL

  # Next, we just rewrite it using Arel
  # Define tables, which we are going to work with: a cte table named descedants,
  # and existing join table suppliers_parent_suppliers
  descedants_cte = Arel::Table.new(:descedants)
  parentships = Arel::Table.new(:suppliers_parent_suppliers)

  # The anchor query is an entry point for a recursion - here we select all records
  # where current supplier is defined as a parent:
  anchor_query = parentships
                   .project(parentships[:supplier_id], parentships[:parent_supplier_id])
                   .where(parentships[:parent_supplier_id].eq(id))

  # A recursive query is a query, which is being executed until it returns an empty set.
  # Every iteration it is executed with rows from the results, added in a previous iteration
  recursive_query = parentships
                      .project(parentships[:supplier_id], parentships[:parent_supplier_id])
                      .from([parentships, descedants_cte])
                      .where(parentships[:parent_supplier_id].eq(descedants_cte[:supplier_id]))

  # Here we actually define a descedants table as a union of anchor and recursive query and give it an alias
  composed_cte = Arel::Nodes::As.new(descedants_cte, anchor_query.union(recursive_query))
  
  # Next we define an expression to query all supplier ids from our CTE table,
  # and this is where we generate WITH RECURSIVE statement
  descedants_ids = descedants_cte
                     .project(descedants_cte[:supplier_id])
                     .with(:recursive, composed_cte)

  # Finally, take all the suppliers with matching ids
  Supplier.distinct.where(Supplier.arel_table[:id].in(descedants_ids))
end