Group orders by customers and products

group_query_example.rb

# frozen_string_literal: true

require 'bundler/inline'

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

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem 'rails'
  gem 'sqlite3'
  gem 'pry', require: true
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')
ActiveRecord::Base.logger = Logger.new($stdout)

ActiveRecord::Schema.define do
  create_table :customers, force: true do |t|
    t.string :name
  end

  create_table :products, force: true do |t|
    t.string :name
  end

  create_table :orders, force: true do |t|
    t.references :customer, null: false, foreign_key: true
  end

  create_table :order_lines, force: true do |t|
    t.references :order, null: false, foreign_key: true
    t.references :product, null: false, foreign_key: true

    t.integer :quantity
    t.decimal :unit_price, precision: 13, scale: 2

    t.timestamps
  end
end

class Customer < ActiveRecord::Base
  has_many :orders
end

class Product < ActiveRecord::Base
  has_many :order_lines
end

class Order < ActiveRecord::Base
  belongs_to :customer
  has_many :order_lines
end

class OrderLine < ActiveRecord::Base
  belongs_to :order
  belongs_to :product
end

class BugTest < Minitest::Test
  def test_query
    john = Customer.create!(name: 'John')
    mary = Customer.create!(name: 'Mary')

    apple = Product.create!(name: 'Apple')
    banana = Product.create!(name: 'Banana')
    orange = Product.create!(name: 'Orange')

    order1 = Order.create!(customer: john)
    order1.order_lines.create!(product: apple, quantity: 5, unit_price: 0.50)
    order1.order_lines.create!(product: banana, quantity: 2, unit_price: 1.00)
    order1.order_lines.create!(product: orange, quantity: 10, unit_price: 2.50)

    order2 = Order.create!(customer: mary)
    order2.order_lines.create!(product: apple, quantity: 1, unit_price: 0.50)
    order2.order_lines.create!(product: banana, quantity: 4, unit_price: 1.00)
    order2.order_lines.create!(product: orange, quantity: 2, unit_price: 2.50)

    actual = OrderLine.joins(:order, :product, 'INNER JOIN customers on customers.id = orders.id')
                      .group('customers.id, products.id')
                      .where('order_lines.created_at >= ?', Date.today.beginning_of_year)
                      .pluck(
                        :customer_id,
                        :product_id,
                        :unit_price,
                        'customers.name',
                        'products.name',
                        'sum(order_lines.quantity)'
                      )

    assert_equal 6, actual.count

    expected = [
      [john.id, apple.id, 0.50, john.name, 'Apple', 5],
      [john.id, banana.id, 1.00, john.name, 'Banana', 2],
      [john.id, orange.id, 2.50, john.name, 'Orange', 10],
      [mary.id, apple.id, 0.50, mary.name, 'Apple', 1],
      [mary.id, banana.id, 1.00, mary.name, 'Banana', 4],
      [mary.id, orange.id, 2.50, mary.name, 'Orange', 2]
    ]

    assert_equal expected, actual

    # or a raw query:
    query = <<~SQL.squish
      SELECT
          customers.id,
          products.id,
          order_lines.unit_price,
          customers.name,
          products.name,
          sum(order_lines.quantity)
      FROM order_lines INNER JOIN orders ON orders.id = order_lines.order_id
      INNER JOIN products ON products.id = order_lines.product_id
      INNER JOIN customers on customers.id = orders.id
      WHERE order_lines.created_at >= '2022-01-01'
      GROUP BY customers.id, products.id
    SQL

    actual = ActiveRecord::Base.connection.exec_query(query)

    assert_equal expected, actual.rows
  end
end