rom_sql_custom_order_for_has_many.rb

#!/usr/bin/env ruby

require "rom-sql"
require "rom-repository"
require "dry-types"

module Relations
  class Charts < ROM::Relation[:sql]
    schema(:charts, infer: true) do
      associations do
        has_many :questions, through: :questions_charts
      end
    end

    view(:ordered) do
      schema do
        append(relations[:questions_charts][:order])
      end

      relation do
        order(:order)
      end
    end
  end

  class QuestionsCharts < ROM::Relation[:sql]
    schema(:questions_charts, infer: true) do
      associations do
        belongs_to :questions
        belongs_to :charts
      end
    end
  end

  class Questions < ROM::Relation[:sql]
    schema(:questions, infer: true) do
      associations do
        has_many :questions_charts
        has_many :charts, through: :questions_charts, view: :ordered
      end
    end
  end
end

config = ROM::Configuration.new(:sql, "sqlite::memory")
config.register_relation Relations::Charts
config.register_relation Relations::Questions
config.register_relation Relations::QuestionsCharts

config.gateways[:default].tap do |gateway|
  migration = gateway.migration do
    change do
      create_table :charts do
        primary_key :id
        string :type, null: true
      end

      create_table :questions do
        primary_key :id
        string :title, null: true
      end

      create_table :questions_charts do
        primary_key :id
        foreign_key :question_id, :questions, null: false
        foreign_key :chart_id, :charts, null: false
        integer :order, null: true
      end
    end
  end
  migration.apply gateway.connection, :up
end

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

connection = config.gateways[:default].connection
connection.execute "INSERT INTO charts (type) VALUES ('bar')"
connection.execute "INSERT INTO questions (title) VALUES ('My question')"
connection.execute "INSERT INTO questions_charts (question_id, chart_id, 'order') VALUES (1, 1, 10)"

container = ROM.container(config)

module Repositories
  class Question < ROM::Repository[:questions]
    relations :charts

    def with_charts
      aggregate(:charts)
    end
  end
end

repo = Repositories::Question.new(container)

puts repo.with_charts.to_a.inspect
# [#<ROM::Struct[Question] id=1 title="My question" charts=[#<ROM::Struct[Chart] id=1 type="bar" question_id=1 order=10>]>]