61
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated at

Organization

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

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
61
Help us understand the problem. What are the problem?