rom-example.rb

#!/usr/bin/env ruby

require 'bundler/inline'

gemfile(true) do
  source 'https://rubygems.org'

  gem 'pg'
  gem 'sequel_pg', require: 'sequel'
  gem 'rom', '~> 5'
  gem 'rom-sql'
  gem 'pry'
end

require 'pry'
require 'open3'

system "psql -c 'CREATE DATABASE rom_example'"

o, e, s = Open3.capture3('psql rom_example', stdin_data: <<~SQL)
  CREATE schema public;

  CREATE TABLE public.foos (
    id bigint NOT NULL,
    data jsonb NOT NULL default '{}'
  );
SQL

s.success? ? puts(o) : abort(e)

require 'sequel'
require 'rom'
require 'rom/sql'

ROM::SQL.load_extensions :postgres

config = ROM::Configuration.new(:sql, "postgres://localhost:5432/rom_example", extensions: %i[pg_json])

config.relation(:foos) do
  schema(infer: true)
  auto_struct true
end

rom = ROM.container(config)
foos = rom.relations[:foos]

foos
  .select { data.pg_jsonb.get_text('user_id').as(:user_id) }
  .select_append { function(:MAX, data.pg_jsonb.get_text('featured_at')).as(:featured_at) }
  .where { data.pg_jsonb.get_text('featured_at') <= Date.today.to_time }
  .group { user_id }
  .order(nil)
  .then { |this| puts this.dataset.sql }

rom.gateways[:default].disconnect
system "psql -c 'DROP DATABASE rom_example'"