custom_assoc_view.rb

require 'rom-sql'
require 'rom-repository'

rom = ROM.container(:sql, 'sqlite::memory') do |c|
  c.gateways[:default].create_table :things do
    primary_key :id
    column :first_id, Integer
    column :second_id, Integer
    column :name, String
  end

  c.gateways[:default].create_table :drawers do
    primary_key :id
    column :first_id, Integer
    column :second_id, Integer
    column :name, String
  end

  c.gateways[:default].use_logger(Logger.new($stdout))

  c.relation(:things) do
    schema(infer: true)

    view(:for_drawers) do
      schema do
        append(relations[:drawers][:id].as(:drawer_id)).qualified
      end

      relation do |drawers|
        things.
          join(:drawers,
               drawers.source[:first_id].qualified => self[:first_id].qualified,
               drawers.source[:second_id].qualified => self[:second_id].qualified).
          where(drawers.source[:id].qualified => drawers.pluck(:id))
      end
    end
  end

  c.relation(:drawers) do
    schema(infer: true)
  end
end

rom.relations[:drawers].insert name: 'First Drawer', first_id: 1, second_id: 2
rom.relations[:drawers].insert name: 'Second Drawer', first_id: 3, second_id: 4

rom.relations[:things].insert(name: 'A thing from the first drawer', first_id: 1, second_id: 2)
rom.relations[:things].insert(name: 'A thing from the second drawer', first_id: 3, second_id: 4)

class DrawerRepo < ROM::Repository[:drawers]
  relations :things
end

drawer_repo = DrawerRepo.new(rom)

puts drawer_repo.drawers.combine(many: drawer_repo.things.for_drawers).to_a.inspect
# SELECT `id`, `first_id`, `second_id`, `name` FROM `drawers` ORDER BY `drawers`.`id`
# SELECT `things`.`id`, `things`.`first_id`, `things`.`second_id`, `things`.`name`, `drawers`.`id` AS 'drawer_id' FROM `things` INNER JOIN `drawers` ON ((`drawers`.`first_id` = `things`.`first_id`) AND (`drawers`.`second_id` = `things`.`second_id`)) WHERE (`drawers`.`id` IN (1, 2)) ORDER BY `things`.`id`
# [#<ROM::Struct[Drawer] id=1 first_id=1 second_id=2 name="First Drawer" things=[#<ROM::Struct[Thing] id=1 first_id=1 second_id=2 name="A thing from the first drawer" drawer_id=1>]>, #<ROM::Struct[Drawer] id=2 first_id=3 second_id=4 name="Second Drawer" things=[#<ROM::Struct[Thing] id=2 first_id=3 second_id=4 name="A thing from the second drawer" drawer_id=2>]>]

This has worked really well for me, but it appears a little brittle. If I change it so that the relation and table for drawers are not named the same (as I have in my data model) then it no longer pulls out the things in the drawers association.

I expect that it is due to naming for the appended id in for_drawers but I can't see how to get around that. This has meant that I need to have a specific name for my "drawers" table that doesn't conflict with the key used.

Any thoughts?

#!/usr/bin/env ruby
require 'rom-sql'
require 'rom-repository'

rom = ROM.container(:sql, 'sqlite::memory') do |c|
  c.gateways[:default].create_table :things do
    primary_key :id
    column :first_id, String
    column :second_id, String
    column :name, String
  end

  c.gateways[:default].create_table :firsts do
    primary_key :id
    column :first_id, String
    column :second_id, String
    column :name, String
  end

  c.gateways[:default].use_logger(Logger.new($stdout))

  c.relation(:things) do
    schema(infer: true)

    view(:for_drawers) do
      schema do
        append(relations[:drawers][:id].as(:firsts_id)).qualified
      end

      relation do |drawers|
        things.
          join(:firsts,
               drawers.source[:first_id].qualified => self[:first_id].qualified,
               drawers.source[:second_id].qualified => self[:second_id].qualified).
          where(drawers.source[:id].qualified => drawers.pluck(:id))
      end
    end
  end

  c.relation(:drawers) do
    register_as :drawers
    schema(:firsts, infer: true)
  end
end

rom.relations[:drawers].insert name: 'First Drawer', first_id: "1", second_id: "2"
rom.relations[:drawers].insert name: 'Second Drawer', first_id: "3", second_id: "4"

rom.relations[:things].insert(name: 'A thing from the first drawer', first_id: "1", second_id: "2")
rom.relations[:things].insert(name: 'A thing from the second drawer', first_id: "3", second_id: "4")

class DrawerRepo < ROM::Repository[:drawers]
  relations :things
end

drawer_repo = DrawerRepo.new(rom)

puts drawer_repo.drawers.combine(many: drawer_repo.things.for_drawers).to_a.inspect
I, [2017-02-10T16:43:54.398242 #76000]  INFO -- : (0.000127s) SELECT `id`, `first_id`, `second_id`, `name` FROM `firsts` ORDER BY `firsts`.`id`
I, [2017-02-10T16:43:54.399497 #76000]  INFO -- : (0.000200s) SELECT `things`.`id`, `things`.`first_id`, `things`.`second_id`, `things`.`name`, `firsts`.`id` AS 'firsts_id' FROM `things` INNER JOIN `firsts` ON ((`firsts`.`first_id` = `things`.`first_id`) AND (`firsts`.`second_id` = `things`.`second_id`)) WHERE (`firsts`.`id` IN (1, 2)) ORDER BY `things`.`id`
[#<ROM::Struct[First] id=1 first_id="1" second_id="2" name="First Drawer" things=[]>, #<ROM::Struct[First] id=2 first_id="3" second_id="4" name="Second Drawer" things=[]>]

You can specify combine keys explicitly ie:

drawer_repo.drawers.combine(many: { drawers: [drawer_repo.things.for_drawers, key_on_parent_side: key_on_child_side] })

@solnic, thanks - I've made it work on that basis.