Sequel #with_recursive CTE

app.rb

DB = Sequel.connect('jdbc:sqlite::memory:')

DB.create_table(:categories) do
  Integer :id, :primary_key => true
  foreign_key :parent_id, :categories
  String :name, :null => false
end
DB[:categories].import([:id, :parent_id, :name], [[1, nil, 'GPS'], [2, 1, 'Vehicle GPS'], [3, 1, 'Handheld GPS'], [4, nil, 'GPS Accessories']])

DB.create_table(:products) do
  Integer :id, :primary_key => true
  foreign_key :category_id, :categories, :null => false
  String :name, :null => false
  Float :price, :null => false
end
DB[:products].import([:id, :category_id, :name, :price], [[1, 2, 'Nuvi', 99.99], [2, 3, 'eTrex', 99.99], [3, 3, 'Montana', 99.99], [4, 4, 'Carrying Case', 29.99]])

cte = DB[:c].
    with_recursive(:c,
                   DB[:categories].
                       select(:id, :parent_id).
                       where(:id => DB[:products].where { price < 100.0 }.select(:category_id)),
                   DB[:c].join(:categories, :id => :parent_id).select(:categories__id, :categories__parent_id))

pp cte.all

#[{:id=>1, :parent_id=>nil, :name=>"GPS"},
# {:id=>4, :parent_id=>nil, :name=>"GPS Accessories"}]