Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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
jnchito
SIer、社内SEを経て、ソニックガーデンに合流したプログラマ。 「プロを目指す人のためのRuby入門」の著者。 http://gihyo.jp/book/2017/978-4-7741-9397-7 および「Everyday Rails - RSpecによるRailsテスト入門」の翻訳者。 https://leanpub.com/everydayrailsrspec-jp
https://blog.jnito.com/
sonicgarden
「お客様に無駄遣いをさせない受託開発」と「習慣を変えるソフトウェアのサービス」に取り組んでいるソフトウェア企業
http://www.sonicgarden.jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away