Rails❤️SQL #railsdm

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

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 'PMMPX
    NF