Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Rails❤️SQL #railsdm

Junichi Ito
December 09, 2017

Rails❤️SQL #railsdm

Rails Developers Meetup 2017で使用したスライドです。
https://techplay.jp/event/631431
https://qiita.com/jnchito/items/625bef4187e360d7f4bc

Junichi Ito

December 09, 2017
Tweet

More Decks by Junichi Ito

Other Decks in Programming

Transcript

  1. Πϝʔδ class Project < ApplicationRecord belongs_to :customer has_many :memberships has_many

    :members, through: :memberships ݕࡧର৅ʹνΣοΫແ͠ = ͢΂ͯର৅
 ݕࡧର৅ʹνΣοΫ͋Γ = ͦͷModel͕ର৅
  2. ίʔυྫʢFormϞσϧͱControllerͱViewʣ class ProjectSearchForm include ActiveModel::Model attr_accessor :keyword, :project, :customer, :member

    # ... <%= 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 %> 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 # ... end ը໘ͷೖྗ஋͕ೖΔ FormϞσϧͷ֤߲໨ʹ஋Λೖྗ͢Δ resultϝιουͰΫΤϦ࣮ߦʢޙड़ʣ
  3. ίʔυྫʢFormϞσϧͷSQLߏங ʣ 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 (target_none?΍xxx_selected?
 ͸ಠࣗͷprivateϝιου) ೖྗ஋͕͋Ε͹ݕࡧ৚݅Λ௥Ճ ݕࡧ৚݅ΛORͰ࿈݁͠
 whereʹSQLͱόΠϯυ
 ύϥϝʔλΛ౉͢
  4. Πϝʔδ class User < ApplicationRecord has_many :billings # ੥ٻཤྺ has_many

    :payments # ೖۚཤྺ ߦຖʹ੥ٻσʔλͱೖۚσʔλ͕੾ΓସΘͬͨΓɺ
 ࢒ߴͷྦྷੵ஋Λදࣔͨ͠Γ
  5. ίʔυྫʢ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 %> ) WITH۟ʢڞ௨ςʔϒϧࣜʣͰҰछͷϏϡʔΛ࡞Δ ੥ٻ৘ใʢ্ʣͱೖۚ৘ใʢԼʣΛUNION͢Δ ը໘Ͱ໊લΛࢦఆ͞ΕͨΒ৚݅Λ௥Ճ
  6. ίʔυྫʢERB - ޙ൒ʣ 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 Windowؔ਺Ͱྦྷੵ஋Λܭࢉ લ൒ͷWITH۟Ͱ࡞ͬͨϏϡʔΛར༻
  7. ίʔυྫʢ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 ERBʹը໘ͷೖྗ஋ΛҾ͖౉͠
 ಈతʹSQLΛߏங όΠϯυύϥϝʔλΛ૊ΈࠐΜͰ
 Connection͔ΒSQLΛ࣮ߦ ը໘ͷೖྗ஋Λड͚औΕΔΑ͏ʹ͓ͯ͘͠
  8. ίʔυྫʢControllerͱViewʣ class BalanceReportsController < ApplicationController def index @balance_report = BalanceReport.new(balance_report_params)

    @result = @balance_report.result end # ... end <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ͷ࣮ߦ݁ՌΛView΁౉͢ ߲໨໊ͱ֤ߦͷ߲໨஋Λग़ྗ
  9. Ԡ༻ʢSQLΛDRYʹ͍ͨ͠໰୊ʣ TRUNC(CASE half_flag 
 WHEN ’t’ THEN price / 2


    ELSE price END) TRUNC(TRUNC(CASE half_flag 
 WHEN ’t’ THEN price / 2 
 ELSE price END * 0.08)) TRUNC(CASE half_flag WHEN ’t’ THEN price / 2 ELSE price END) + TRUNC(TRUNC(CASE half_flag WHEN ’t’ THEN price / 2 ELSE price END * 0.08)) (TRUNC(CASE … * 0.08))) * SUM(amount) SUM(amount)
  10. ࣮૷ํ਑ ▸ ࠶ར༻͍ͨ͠ܭࢉࣜΛม਺ʹೖΕΔ ▸ ม਺͸ࣜల։ͯ͠࠶ར༻͢Δ 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)"
  11. ίʔυྫʢERBʣ SELECT i.name AS "঎඼໊", i.price AS "ఆՁ", i.half_flag AS

    "൒ֹϑϥά", <%= without_tax = "TRUNC(CASE i.flag = 't' WHEN 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 ܭࢉࣜΛม਺ʹ์ΓࠐΜͰ͍͘ ੫ࠐΈՁ֨ × ച্ຊ਺ = ച্ֹͱಡΊΔ
  12. ࣮૷ํ਑ ▸ SQLҰൃͰINSERT / UPDATE͢Δ ▸ INSERT + SELECT ΍

    UPDATE + SELECT Λ׆༻͢Δ ▸ ConnectionΦϒδΣΫτ͔Β௚઀SQLΛ࣮ߦ͢Δ
  13. ίʔυྫ 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 UPDATE + SELECTͰSQLΛ࡞੒ (੥ٻֹ - ೖֹۚ = 0ͳΒ
 ೖۚ׬ྃϑϥά͕true) SQLΛ࣮ߦ͠ɺߋ৽͞Εͨߦ਺Λฦ͢