RAILS❤SQL

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

αϯϓϧίʔυ͸ͪ͜Β ▸ https://qiita.com/jnchito/items/625bef4187e360d7f4bc

ΠϯτϩμΫγϣϯ

ActiveRecordͬͯΊͬͪΌศརʂ ▸ ݕࡧ༻ɺߋ৽༻ͷAPI͕͘͢͝๛෋ ▸ findɺwhereɺscopeɺsaveɺupdate_allɺetc… ▸ Ransack gem΋ΊͬͪΌศརʂ ▸ ͍ΖΜͳݕࡧϑΥʔϜ͕࡞ΕΔ ▸ ͜ͷ2͕ͭ͋Ε͹ɺ9ׂҎ্ͷϢʔεέʔε͸ରԠՄೳ

ରԠͮ͠Β͍1ׂະຬ͸Կʁ ▸ ࠓճ͓࿩͢Δͷ͸ҎԼͷ3ύλʔϯ ▸ ͪΐͬͱڽͬͨݕࡧ৚݅ ▸ ෳࡶͳूܭॲཧ ▸ େྔσʔλͷҰׅߋ৽

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

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

1. ͪΐͬͱڽͬͨݕࡧ৚݅

Πϝʔδ class Project < ApplicationRecord belongs_to :customer has_many :memberships has_many :members, through: :memberships ݕࡧର৅ʹνΣοΫແ͠ = ͢΂ͯର৅
 ݕࡧର৅ʹνΣοΫ͋Γ = ͦͷModel͕ର৅

࣮૷ํ਑ ▸ ActiveModel::ModelΛincludeͨ͠FormΫϥεΛఆٛ ▸ ೖྗ߲໨͸attr_accessorʹͯ͠Viewͱ࿈ܞՄೳʹ ▸ ೖྗ஋ʹ͋Θͤͯಈతʹݕࡧ৚݅ʢSQLʣΛߏங ▸ ௕ͯ͘టष͍ίʔυʹͳΔ͕ɺॊೈੑ͸ൈ܈ ▸ RelationΛฦ͢ͷͰฒͼସ͑΍ϖʔδωʔγϣϯ΋Մೳ

ίʔυྫʢ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ϝιουͰΫΤϦ࣮ߦʢޙड़ʣ

ίʔυྫʢ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ͱόΠϯυ
 ύϥϝʔλΛ౉͢

2. ෳࡶͳूܭॲཧ

Πϝʔδ class User < ApplicationRecord has_many :billings # ੥ٻཤྺ has_many :payments # ೖۚཤྺ ߦຖʹ੥ٻσʔλͱೖۚσʔλ͕੾ΓସΘͬͨΓɺ
 ࢒ߴͷྦྷੵ஋Λදࣔͨ͠Γ

ෳࡶͳूܭॲཧ ▸ ूܭͷͨΊͷϏδωεϧʔϧ͕ෳࡶ ▸ ActiveRecordͷsum΍averageͰ͸ରԠͰ͖ͳ͍ ▸ 1ߦ=1Modelͷؔ܎͕΄ͱΜͲ่յ͍ͯ͠Δ

࣮૷ํ਑ ▸ ্͔ΒԼ·Ͱશ෦SQLͰॻ͘ ▸ SQL͸ERBʹॻ͘ ▸ ʮݡ͍ϓϨʔϯςΩετʯͱͯ͠ERBΛ࢖͏ ▸ ERBͳΒifจΛॻ͍ͨΓɺม਺Λ࢖ͬͨΓͰ͖Δ ▸ ERBͱ͍ͬͯ΋RailsͷViewͱ͸ແؔ܎ͳʮૉͷERBʯ ▸ ConnectionΦϒδΣΫτ͔Β௚઀SQLΛ࣮ߦ͢Δ

ίʔυྫʢ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͢Δ ը໘Ͱ໊લΛࢦఆ͞ΕͨΒ৚݅Λ௥Ճ

ίʔυྫʢ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۟Ͱ࡞ͬͨϏϡʔΛར༻

ίʔυྫʢ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Λ࣮ߦ ը໘ͷೖྗ஋Λड͚औΕΔΑ͏ʹ͓ͯ͘͠

ίʔυྫʢControllerͱViewʣ class BalanceReportsController < ApplicationController def index @balance_report = BalanceReport.new(balance_report_params) @result = @balance_report.result end # ... end <% @result.fields.each do |field| %> <%= field %> <% end %> <% @result.each do |row| %> <% row.values.each do |value| %> <%= value %> <% end %> <% end %> SQLͷ࣮ߦ݁ՌΛView΁౉͢ ߲໨໊ͱ֤ߦͷ߲໨஋Λग़ྗ

Ԡ༻ʢ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)

࣮૷ํ਑ ▸ ࠶ར༻͍ͨ͠ܭࢉࣜΛม਺ʹೖΕΔ ▸ ม਺͸ࣜల։ͯ͠࠶ར༻͢Δ 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)"

ίʔυྫʢ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 ܭࢉࣜΛม਺ʹ์ΓࠐΜͰ͍͘ ੫ࠐΈՁ֨ × ച্ຊ਺ = ച্ֹͱಡΊΔ

3. େྔσʔλͷҰׅߋ৽

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

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

࣮૷ํ਑ ▸ SQLҰൃͰINSERT / UPDATE͢Δ ▸ INSERT + SELECT ΍ UPDATE + SELECT Λ׆༻͢Δ ▸ ConnectionΦϒδΣΫτ͔Β௚઀SQLΛ࣮ߦ͢Δ

ίʔυྫ 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Λ࣮ߦ͠ɺߋ৽͞Εͨߦ਺Λฦ͢

·ͱΊ

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

SQLɺ ಘҙͰ͔͢ʁ

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

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

RAILS❤SQL

THANK YOU❤

ABOUT ME

JUNICHI ITO Hello, Tokyo!

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

@jnchito blog.jnito.com 'PMMPXNF