posts_and_comments.rb

require 'pathname'

ROOT = Pathname(__FILE__).expand_path.join('../..')

require 'rom'

class Post
  include Equalizer.new :id, :title, :body, :comments
  attr_accessor :id, :title, :body, :comments
end

class Comment
  include Equalizer.new :id, :email, :message, :post
  attr_accessor :id, :email, :message, :post
end

@rom = ROM::Environment.setup(sqlite: "sqlite3://#{ROOT}/tmp/db/sqlite.db") do
  schema do
    base_relation :comments do
      repository :sqlite

      attribute :id, Integer, rename: :comment_id
      attribute :post_id, Integer
      attribute :email, String
      attribute :message, String

      key :id
    end

    base_relation :posts do
      repository :sqlite

      attribute :id, Integer, rename: :post_id
      attribute :title, String
      attribute :body, String

      key :id
    end
  end

  mapping do
    relation :comments do
      model Comment

      map :id, from: :comment_id
      map :post_id
      map :email
      map :message
    end

    relation :posts do
      model Post

      map :id, from: :post_id
      map :title
      map :body
    end
  end
end

posts = @rom[:posts]
comments = @rom[:comments]

puts posts.to_a.inspect

puts posts.
  join(comments).
  group(comments: comments.project([:comment_id, :email, :message])).
  project([:post_id, :title, :body, :comments]).
  to_a.inspect