こんにちは！kossyです！
さて、今回はRailsでunionを使って複数テーブルに跨がるORDER BY を実現する方法について、ブログに残してみたいと思います。
環境
Ruby 2.6.6
Rails 6.0.3.6
POstgreSQL 12系
MacOS Catalina
実装
抽象化した例文が以下かなと。
scope :super_order, -> () { query = <<-SQL.squish SELECT * FROM ( SELECT テーブル1.*, CASE WHEN 条件 then 0 WHEN 条件 then 2 WHEN 条件 then 4 end AS sort FROM テーブル名 as テーブル1 UNION SELECT テーブル2.*, CASE WHEN 条件 then 1 WHEN 条件 then 3 WHEN 条件 then 5 end AS sort FROM テーブル名 as テーブル2 ) AS sorted_orders ORDER BY sort SQL find_by_sql(query) }
CASE文で条件判定して数値を出して、その結果をsortという列名をつけています。
そしてUNIONを使って再度同様の処理をして、最後にsortの値でORDER BY をしています。
UNIONにはいくつか種類があり
・UNION ALL => 和結合
・INTERSECT => 積結合
・EXCEPT => 差結合
の3つがあります。
参考: [SQL] 8. クエリの結合 | TECHSCORE(テックスコア)
POstgreSQLの場合、UNION ALL を指定しない場合は INTERSECTでUNIONする挙動になります。
参考: 7.4. 問い合わせの結合
上記を踏まえて、例として、「役職が上の従業員を上位 & 男女別 で 売り上げを降順」でデータを引いてくるクエリを定義してみました。
scope :super_order, -> () { query = <<-SQL.squish SELECT sort, price, * FROM ( SELECT order_1.*, CASE WHEN pos.name = '支店長' then 0 WHEN pos.name = '課長' then 2 WHEN pos.name = '係長' then 4 WHEN pos.name = '一般社員' then 6 end AS sort FROM orders as order_1 INNER JOIN employees AS emp ON emp.id = order_1.employee_id INNER JOIN positions AS pos ON pos.id = emp.position_id WHERE emp.sex = '0' UNION SELECT order_2.*, CASE WHEN pos.name = '支店長' then 1 WHEN pos.name = '課長' then 3 WHEN pos.name = '係長' then 5 WHEN pos.name = '一般社員' then 7 end AS sort FROM orders as order_2 INNER JOIN employees AS emp ON emp.id = order_2.employee_id INNER JOIN positions AS pos ON pos.id = emp.position_id WHERE emp.sex = '1' ) AS sorted_orders ORDER BY sort, price SQL find_by_sql(query) }
rails cで試してみます。
$ order = Order.super_order.first Order Load (39.9ms) SELECT sort, price, * FROM ( SELECT order_1.*, CASE WHEN pos.name = '支店長' then 0 WHEN pos.name = '課長' then 2 WHEN pos.name = '係長' then 4 WHEN pos.name = '一般社員' then 6 end AS sort FROM orders as order_1 INNER JOIN employees AS emp ON emp.id = order_1.employee_id INNER JOIN positions AS pos ON pos.id = emp.position_id WHERE emp.sex = '0' UNION SELECT order_2.*, CASE WHEN pos.name = '支店長' then 1 WHEN pos.name = '課長' then 3 WHEN pos.name = '係長' then 5 WHEN pos.name = '一般社員' then 7 end AS sort FROM orders as order_2 INNER JOIN employees AS emp ON emp.id = order_2.employee_id INNER JOIN positions AS pos ON pos.id = emp.position_id WHERE emp.sex = '1' ) AS sorted_orders ORDER BY sort, price DESC => #<Order:0x0000558c7b1011b8 id: 2168, customer_name: "Bernier-Gibson", price: 10000000, status: "un_official", ordered_at: Tue, 08 Dec 2020, employee_id: 81, company_id: 1, department_id: 27, created_at: Fri, 19 Feb 2021 16:08:45 UTC +00:00, updated_at: Fri, 19 Feb 2021 16:08:45 UTC +00:00> $ order.sort => 0 $ Order.super_order.pluck(:sort).compact.uniq => [0, 1, 2, 3, 4, 5, 6, 7]
きちんとsort列を基にORDER BY されていることがわかりました。
