Trying to achieve a dynamically filtered left outer join AND eager loading

dynamic-left-outer-join.rb

require 'spec_helper'
require 'sequel'

DATABASE = Sequel::Model.db

DATABASE.drop_table(:orders) rescue nil
DATABASE.drop_table(:order_lines) rescue nil
DATABASE.drop_table(:customers) rescue nil

DATABASE.create_table(:customers) do
  primary_key :id
  String :name
end

DATABASE.create_table(:orders) do
  primary_key :id
  String :invoice_number
  foreign_key :customer_id, :customers
end

DATABASE.create_table(:order_lines) do
  primary_key :id
  String :product_name
  foreign_key :order_id, :orders
end

class Order < Sequel::Model
  many_to_one :customer
end

class OrderLine < Sequel::Model
  many_to_one :order
end

class Customer < Sequel::Model
end


COLUMNS = [:invoice_number, :product_name, :customer_id]
QUERY = DATABASE[:orders].select(*COLUMNS).left_outer_join(:order_lines, { order_id: :id } )

class DenormalisedOrderLine < Sequel::Model(QUERY)
  many_to_one :customer
end

RSpec.describe "Orders with fish and umbrellas" do

  let!(:customer) { Customer.create(name: "Mary") }

  let!(:order_1) { Order.create(invoice_number: "1", customer: customer) }
  let!(:umbrella) { OrderLine.create(product_name: "Umbrella", order: order_1) }
  let!(:fish) { OrderLine.create(product_name: "Fish", order: order_1) }

  let!(:order_2) { Order.create(invoice_number: "2", customer: customer) }
  let!(:umbrella_2) { OrderLine.create(product_name: "Umbrella", order: order_2) }


  context "with a statically defined class" do
    subject { DenormalisedOrderLine.where(:product_name => "Fish").all }

    it "can eager load a customer, hooray!" do
      order_lines = DenormalisedOrderLine.eager(:customer).all
      expect(order_lines.first.customer).to eq customer
    end

    it "returns the order line for the order with a fish" do
      order_line_with_fish = subject.find{ | order_line | order_line.product_name == "Fish" }
      expect(order_line_with_fish).to_not be nil
    end

    it "should return the order line for the order without a fish, but it can't", pending: "can't do this" do
      order_line_with_fish = subject.find{ | order_line | order_line.product_name == nil }
      expect(order_line_with_fish).to_not be nil
    end
  end

  context "with a dynamically defined dataset" do
    let(:dynamically_created_dataset) do
      fish_order_lines = DATABASE[:order_lines].where(product_name: "Fish")
      DATABASE[:orders]
        .select(*COLUMNS)
        .left_outer_join(fish_order_lines, { order_id: :id })
    end

    subject { dynamically_created_dataset.all }

    it "returns the order line for the order with a fish" do
      order_line_with_fish = subject.find{ | order_line | order_line[:product_name] == "Fish" }
      expect(order_line_with_fish).to_not be nil
    end

    it "returns line for the order without a fish, hooray!" do
      order_line_with_fish = subject.find{ | order_line | order_line[:product_name] == nil }
      expect(order_line_with_fish).to_not be nil
    end

    it "does not allow us to eager load data" do
      expect{ dynamically_created_dataset.many_to_one :customer }.to raise_error NoMethodError
    end
  end

  context "with a dynamically defined class" do
    let(:dynamically_created_dataset) do
      fish_order_lines = DATABASE[:order_lines].where(product_name: "Fish")
      DATABASE[:orders]
        .select(*COLUMNS)
        .left_outer_join(fish_order_lines, { order_id: :id })
    end

    let(:dyanmically_created_class) do
      Sequel::Model(dynamically_created_dataset)
    end

    subject { dyanmically_created_class.all }

    it "returns the order line for the order with a fish" do
      order_line_with_fish = subject.find{ | order_line | order_line[:product_name] == "Fish" }
      expect(order_line_with_fish).to_not be nil
    end

    it "returns line for the order without a fish, hooray!" do
      order_line_with_fish = subject.find{ | order_line | order_line[:product_name] == nil }
      expect(order_line_with_fish).to_not be nil
    end

    it "can eager load a customer, hooray!" do
      dyanmically_created_class.many_to_one :customer
      order_lines = dyanmically_created_class.eager(:customer).all
      expect(order_lines.first.customer).to eq customer
    end
  end
end