Edited at

Rails❤️SQLのサンプルコード #railsdm

Rails Developers Meetup 2017で発表した「Rails❤️SQL」のサンプルコードです。


スライド

スライドはこちらにあります。この記事とあわせてどうぞ。

Rails❤️SQL #railsdm // Speaker Deck


1. ちょっと凝った検索条件


Formモデル

target_none?xxx_selected?は独自のprivateメソッドです(コード例は省略)。

class ProjectSearchForm

include ActiveModel::Model

attr_accessor :keyword, :project, :customer, :member

def result
scope = Project.all

if keyword.present?
conditions = []
if target_none? || project_selected?
conditions << "projects.name ILIKE :keyword"
end
if target_none? || customer_selected?
scope = scope.joins(:customer)
conditions << "customers.name ILIKE :keyword"
end
if target_none? || member_selected?
conditions << <<~SQL
EXISTS(
SELECT *
FROM memberships ms
INNER JOIN members m
ON m.id = ms.member_id
WHERE
ms.project_id = projects.id
AND m.name ILIKE :keyword
)
SQL
end
sql = "(#{conditions.join(" OR ")})"
scope = scope.where(sql, keyword: "%#{keyword}%")
end

scope
end

# ...
end


Controller

class ProjectsController < ApplicationController

def index
@project_search_form = ProjectSearchForm.new(project_search_form_params)
@projects = @project_search_form.result.includes(:customer, :members).order(:id)
end

# ...

def project_search_form_params
params
.fetch(:project_search_form, {})
.permit(:keyword, :project, :customer, :member)
end
end


View

<%= form_for @project_search_form, url: root_path, method: :get do |f| %>

<%= f.text_field :keyword %>
<%= f.check_box :project %> プロジェクト名
<%= f.check_box :customer %> 顧客名
<%= f.check_box :member %> メンバー名
<%= f.submit %>
<% end %>


参考:サンプルアプリを動かす場合

発表の中で使ったサンプルアプリはこちらに置いてます。

ローカルで動かしてみたい方はどうぞ。

https://github.com/JunichiIto/search-form-sandbox


2. 複雑な集計処理


ERB

WITH all_data AS (

SELECT
u.id,
u.name,
b.registered_on,
b.charge_amount,
NULL AS payment_amount
FROM billings b
INNER JOIN users u
ON b.user_id = u.id
WHERE
b.registered_on BETWEEN :date_from AND :date_to
<% if name.present? %>
AND u.name LIKE :name
<% end %>
UNION ALL
SELECT
u.id,
u.name,
p.registered_on,
NULL AS charge_amount,
p.payment_amount
FROM payments b
INNER JOIN users u
ON p.user_id = u.id
WHERE
p.registered_on BETWEEN :date_from AND :date_to
<% if name.present? %>
AND u.name LIKE :name
<% end %>
)
SELECT
id AS "顧客ID",
name AS "顧客名",
registered_on AS "日付",
charge_amount AS "請求額",
payment_amount AS "入金額",
SUM(charge_amount - payment_amount)
OVER (PARTITION BY id ORDER BY registered_on) AS "残高"
FROM all_data
ORDER BY
id,
registered_on


Reportモデル

class BalanceReport

include ActiveModel::Model

attr_accessor :date_from, :date_to, :name

def result
template = File.read(Rails.root.join('app/sqls/balance_report.sql.erb'))
namespace = OpenStruct.new(name: name)
sql = ERB.new(template).result(namespace.instance_eval { binding })
# Ruby 2.5
# sql = ERB.new(template).result_with_hash(name: name)

args = [
sql,
date_from: date_from,
date_to: date_to,
name: name,
]
query = ApplicationRecord.send(:sanitize_sql_array, args)
ApplicationRecord.connection.execute(query)
end
end


Controller

class BalanceReportsController < ApplicationController

def index
@balance_report = BalanceReport.new(balance_report_params)
@result = @balance_report.result
end

# ...
end


View

<table>

<tr>
<% @result.fields.each do |field| %>
<td>
<%= field %>
</td>
<% end %>
</tr>
<% @result.each do |row| %>
<tr>
<% row.values.each do |value| %>
<td>
<%= value %>
</td>
<% end %>
</tr>
<% end %>
</table>


応用(SQLをDRYにしたい問題)

without_tax = "CASE i.flag WHEN 't' THEN i.price / 2 ELSE i.price END"

tax = "#{without_tax} * 0.08"

with_tax = "(#{without_tax} + #{tax})"
#=> "(CASE i.flag WHEN 't' THEN i.price / 2 ELSE i.price END + CASE i.flag WHEN 't' THEN i.price / 2 ELSE i.price END * 0.08)"

SELECT

i.name AS "商品名",
i.price AS "定価",
i.half_flag AS "半額フラグ",
<%= without_tax =
"TRUNC(CASE i.flag WHEN 't' THEN i.price / 2 ELSE i.price END)" %> AS "税抜価格",
<%= tax = "TRUNC(#{without_tax} * 0.08)" %> AS "消費税",
<%= with_tax = "(#{without_tax} + #{tax})" %> AS "税込価格",
<%= amount_sum = "SUM(s.amount)" %> AS "売上本数",
<%= "#{with_tax} * #{amount_sum}" %> AS "売上額"
FROM
items i
INNER JOIN sales s
ON s.item_id = i.id
WHERE
s.reported_on BETWEEN :date_from AND :date_to
GROUP BY
i.id
ORDER BY
i.id


3. 大量データの一括更新

class BillingUpdater

def self.bulk_update_completed_column
sql = <<~SQL
UPDATE billings
SET
completed =
CASE billings.amount - p.amount
WHEN 0 THEN 't'
ELSE 'f'
END
FROM
payments p
WHERE
p.billing_id = billings.id
SQL
result = ApplicationRecord.connection.execute(sql)
# return updated row count
result.cmd_tuples
end
end