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 %> 

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

発表の中で使ったサンプルアプリはこちらに置いてます。
ローカルで動かしてみたい方はどうぞ。

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