1. Problem

I have a self reference table through parent_id column.
The requirement is get all the children record of selected record.

2. Using recursion in sql (mysql >= 8.0)

def self.descendant
  sql = <<-SQL
    with recursive cte (id, parent_id) as (
      select id, parent_id
      from tables
      where parent_id IN (#{ids.join(',')})
      union all
      select i.id, i.parent_id
      from tables as i
      inner join cte
      on i.parent_id = cte.id
    )
    select id from cte
  SQL
  
  Table.execute_sql(sql).to_a.flatten
end

This command will self join until the join target table is empty.

3. The query structure

This query contains 3 parts:

Anchor member: This is the initial query.

select id, parent_id
      from tables
      where parent_id IN (#{ids.join(',')})

Recursive query: This query will self join the previous result table (cte) with the original table.

select i.id, i.parent_id
      from tables as i
      inner join cte
      on i.parent_id = cte.id

UNION ALL: Union the results to 1 final table.

First, the anchor member will return a table, this table will be used as recursive query params, after that the result of recursive query params will be the params of next recursive query, ...
It will stop if recursive query return empty table (the last recursive query return 0 record).
UNION ALL will be called and union all the tables to 1 final table.

Demo: Link