1. Vấn đề:

Mình có một bảng có quan hệ với chính nó qua trường parent_id
Yêu cầu là phải lấy hết các bản ghi con của bản ghi được chọn

2. Sử dụng đệ quy trong 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

Câu lệnh này sẽ từ self join lại với chính bảng tables tới khi nào nó join với bảng rỗng thì sẽ dừng.

3. Phân tích câu lệnh.

Câu lệnh này gồm 3 phần:

Anchor member: Là câu lệnh khởi tạo ban đầu.

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

Recursive query: Một câu lệnh self join tới chính bảng CTE.

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

UNION ALL: Gom hết các kết quả trả về với nhau.

Ban đầu sẽ thực thi Anchor member đầu tiên kết quả trả về từ anchor member sẽ là 1 bảng, bảng này sử dụng làm đầu vào cho recursive query và kết quả trả về của recursive query sẽ là đầu vào cho chính nó tiếp sau, ...
Cho tới khi recursive query trả ra bảng rỗng (không có bản ghi nào) thì sẽ dừng
Lệnh UNION ALL sẽ được gọi và kết hợp các kết quả trước đó với nhau

Demo: Link