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

Rails❤️SQL #railsdm

48a913a2e3bb5e68aae6f73079648e84?s=47 Junichi Ito
December 09, 2017

Rails❤️SQL #railsdm

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

48a913a2e3bb5e68aae6f73079648e84?s=128

Junichi Ito

December 09, 2017
Tweet

Transcript

  1. RAILS❤SQL

  2. ͜Ε͔Β࿩͢͜ͱ ▸ ActiveRecord΍Ransack͸ΊͬͪΌศརʂ ▸ 9ׂҎ্͸ΧόʔͰ͖Δ͕ɺۤखͳϢʔεέʔε΋͋Δ ▸ SQL͕ಘҙͳ͜ͱ͸SQLʹ΍ΒͤΑ͏ ▸ టष͍ίʔυʹͳΓ΍͍͕͢ɺͦͷͿΜॊೈੑ͸ൈ܈ ▸

    ActiveRecordͱSQLΛ͏·͘࢖͍෼͚Α͏ ▸ ͨͩ͠ɺSQLͷॻ͖ํ͸આ໌͠·ͤΜ
  3. αϯϓϧίʔυ͸ͪ͜Β ▸ https://qiita.com/jnchito/items/625bef4187e360d7f4bc

  4. ΠϯτϩμΫγϣϯ

  5. ActiveRecordͬͯΊͬͪΌศརʂ ▸ ݕࡧ༻ɺߋ৽༻ͷAPI͕͘͢͝๛෋ ▸ findɺwhereɺscopeɺsaveɺupdate_allɺetc… ▸ Ransack gem΋ΊͬͪΌศརʂ ▸ ͍ΖΜͳݕࡧϑΥʔϜ͕࡞ΕΔ

    ▸ ͜ͷ2͕ͭ͋Ε͹ɺ9ׂҎ্ͷϢʔεέʔε͸ରԠՄೳ
  6. ରԠͮ͠Β͍1ׂະຬ͸Կʁ ▸ ࠓճ͓࿩͢Δͷ͸ҎԼͷ3ύλʔϯ ▸ ͪΐͬͱڽͬͨݕࡧ৚݅ ▸ ෳࡶͳूܭॲཧ ▸ େྔσʔλͷҰׅߋ৽

  7. SQLͰ΍Ε͹
 ͍͍͡Όͳ͍ʂ

  8. ૝ఆ͢ΔΞϓϦέʔγϣϯ ▸ ςʔϒϧͷϨίʔυ਺͸ଟͯ͘਺ेສ͙Β͍ ▸ ԯΛ௒͑ͨΓ͢Δͱผͷ໰୊͕ൃੜͦ͠͏ ▸ චऀ͕աڈʹߏஙͨ͠RailsΞϓϦ͕ج४ ▸ ιγϟή΍େن໛SNSͳͲͰ͸࿩͕มΘͬͯ͘Δ͔΋ ▸

    RDBMS͸PostgreSQL ▸ جຊతͳ࣮૷ํ਑͸ଞͷRDBMSͰ΋ద༻Ͱ͖Δ͸ͣ
  9. 1. ͪΐͬͱڽͬͨݕࡧ৚݅

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

    :members, through: :memberships ݕࡧର৅ʹνΣοΫແ͠ = ͢΂ͯର৅
 ݕࡧର৅ʹνΣοΫ͋Γ = ͦͷModel͕ର৅
  11. ࣮૷ํ਑ ▸ ActiveModel::ModelΛincludeͨ͠FormΫϥεΛఆٛ ▸ ೖྗ߲໨͸attr_accessorʹͯ͠Viewͱ࿈ܞՄೳʹ ▸ ೖྗ஋ʹ͋Θͤͯಈతʹݕࡧ৚݅ʢSQLʣΛߏங ▸ ௕ͯ͘టष͍ίʔυʹͳΔ͕ɺॊೈੑ͸ൈ܈ ▸

    RelationΛฦ͢ͷͰฒͼସ͑΍ϖʔδωʔγϣϯ΋Մೳ
  12. ίʔυྫʢ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ϝιουͰΫΤϦ࣮ߦʢޙड़ʣ
  13. ίʔυྫʢ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ͱόΠϯυ
 ύϥϝʔλΛ౉͢
  14. 2. ෳࡶͳूܭॲཧ

  15. Πϝʔδ class User < ApplicationRecord has_many :billings # ੥ٻཤྺ has_many

    :payments # ೖۚཤྺ ߦຖʹ੥ٻσʔλͱೖۚσʔλ͕੾ΓସΘͬͨΓɺ
 ࢒ߴͷྦྷੵ஋Λදࣔͨ͠Γ
  16. ෳࡶͳूܭॲཧ ▸ ूܭͷͨΊͷϏδωεϧʔϧ͕ෳࡶ ▸ ActiveRecordͷsum΍averageͰ͸ରԠͰ͖ͳ͍ ▸ 1ߦ=1Modelͷؔ܎͕΄ͱΜͲ่յ͍ͯ͠Δ

  17. ࣮૷ํ਑ ▸ ্͔ΒԼ·Ͱશ෦SQLͰॻ͘ ▸ SQL͸ERBʹॻ͘ ▸ ʮݡ͍ϓϨʔϯςΩετʯͱͯ͠ERBΛ࢖͏ ▸ ERBͳΒifจΛॻ͍ͨΓɺม਺Λ࢖ͬͨΓͰ͖Δ ▸

    ERBͱ͍ͬͯ΋RailsͷViewͱ͸ແؔ܎ͳʮૉͷERBʯ ▸ ConnectionΦϒδΣΫτ͔Β௚઀SQLΛ࣮ߦ͢Δ
  18. ίʔυྫʢ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͢Δ ը໘Ͱ໊લΛࢦఆ͞ΕͨΒ৚݅Λ௥Ճ
  19. ίʔυྫʢ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۟Ͱ࡞ͬͨϏϡʔΛར༻
  20. ίʔυྫʢ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Λ࣮ߦ ը໘ͷೖྗ஋Λड͚औΕΔΑ͏ʹ͓ͯ͘͠
  21. ίʔυྫʢ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΁౉͢ ߲໨໊ͱ֤ߦͷ߲໨஋Λग़ྗ
  22. Ԡ༻ʢ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)
  23. ࣮૷ํ਑ ▸ ࠶ར༻͍ͨ͠ܭࢉࣜΛม਺ʹೖΕΔ ▸ ม਺͸ࣜల։ͯ͠࠶ར༻͢Δ 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)"
  24. ίʔυྫʢ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 ܭࢉࣜΛม਺ʹ์ΓࠐΜͰ͍͘ ੫ࠐΈՁ֨ × ച্ຊ਺ = ച্ֹͱಡΊΔ
  25. 3. େྔσʔλͷҰׅߋ৽

  26. େྔσʔλͷҰׅߋ৽ͷྫ ▸ ఆظతʹεφοϓγϣοτͱͯ͠ಛఆͷσʔλΛίϐʔ ▸ ௥Ճͨ͠ΧϥϜʹରͯ͠ಛఆͷϧʔϧͰσʔλΛߋ৽ ▸ eachͰճ͢ͱ஗͔ͬͨΓϝϞϦΛ৯ͬͨΓ͢Δέʔε ▸ ର৅σʔλ͕Կສ݅΋͋Δ৔߹ͳͲ

  27. ࠓճ૝ఆ͢ΔϢʔεέʔε ▸ ੥ٻςʔϒϧʹೖۚ׬ྃΧϥϜΛ௥Ճͨ͠ ▸ طଘσʔλʹରͯ͠ɺೖ͕ۚ׬͍ྃͯ͠Ε͹trueΛηοτ ▸ migration࣮ߦޙʹσʔλߋ৽༻ͷεΫϦϓτΛ࣮ߦ ▸ ҟ࿦͕ग़Δ͔΋͠Ε·ͤΜ͕ɺ͋͘·Ͱྫͱ͍͏͜ͱͰ

  28. ࣮૷ํ਑ ▸ SQLҰൃͰINSERT / UPDATE͢Δ ▸ INSERT + SELECT ΍

    UPDATE + SELECT Λ׆༻͢Δ ▸ ConnectionΦϒδΣΫτ͔Β௚઀SQLΛ࣮ߦ͢Δ
  29. ίʔυྫ 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Λ࣮ߦ͠ɺߋ৽͞Εͨߦ਺Λฦ͢
  30. ·ͱΊ

  31. ·ͱΊ ▸ ActiveRecord΍Ransack͸ΊͬͪΌศརʂ ▸ 9ׂҎ্͸ΧόʔͰ͖Δ͕ɺۤखͳϢʔεέʔε΋͋Δ ▸ SQL͕ಘҙͳ͜ͱ͸SQLʹ΍ΒͤΑ͏ ▸ టष͍ίʔυʹͳΓ΍͍͕͢ɺͦͷͿΜॊೈੑ͸ൈ܈ ▸

    ActiveRecordͱSQLΛ͏·͘࢖͍෼͚Α͏
  32. SQLɺ ಘҙͰ͔͢ʁ

  33. RailsϓϩάϥϚͷ ͨΊͷSQLೖ໳ ҏ౻ ३Ұ [ஶ]

  34. ͍͔ͭॻ͚ͨΒ
 ͍͍ͳʙʢئ๬ʣ

  35. RAILS❤SQL

  36. THANK YOU❤

  37. ABOUT ME

  38. JUNICHI ITO Hello, Tokyo!

  39. ON SALE! Railsͷษڧ͸ͯ͠Δ͚Ͳɺ
 Ruby୯ମͰษڧ͸͍ͯ͠ͳ͍ɺ
 ͱ͍͏ํʹ͓͢͢Ίʂ

  40. @jnchito blog.jnito.com 'PMMPXNF