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

    View full-size slide

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

    View full-size slide

  3. ΠϯτϩμΫγϣϯ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  6. SQLͰ΍Ε͹

    ͍͍͡Όͳ͍ʂ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  9. Πϝʔδ
    class Project < ApplicationRecord
    belongs_to :customer
    has_many :memberships
    has_many :members, through: :memberships
    ݕࡧର৅ʹνΣοΫແ͠ = ͢΂ͯର৅

    ݕࡧର৅ʹνΣοΫ͋Γ = ͦͷModel͕ର৅

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  12. ίʔυྫʢ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ͱόΠϯυ

    ύϥϝʔλΛ౉͢

    View full-size slide

  13. 2. ෳࡶͳूܭॲཧ

    View full-size slide

  14. Πϝʔδ
    class User < ApplicationRecord
    has_many :billings # ੥ٻཤྺ
    has_many :payments # ೖۚཤྺ
    ߦຖʹ੥ٻσʔλͱೖۚσʔλ͕੾ΓସΘͬͨΓɺ

    ࢒ߴͷྦྷੵ஋Λදࣔͨ͠Γ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  20. ίʔυྫʢ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΁౉͢
    ߲໨໊ͱ֤ߦͷ߲໨஋Λग़ྗ

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  30. SQLɺ
    ಘҙͰ͔͢ʁ

    View full-size slide

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

    View full-size slide

  32. ͍͔ͭॻ͚ͨΒ

    ͍͍ͳʙʢئ๬ʣ

    View full-size slide

  33. THANK YOU❤

    View full-size slide

  34. JUNICHI ITO
    Hello, Tokyo!

    View full-size slide

  35. ON SALE!
    Railsͷษڧ͸ͯ͠Δ͚Ͳɺ

    Ruby୯ମͰษڧ͸͍ͯ͠ͳ͍ɺ

    ͱ͍͏ํʹ͓͢͢Ίʂ

    View full-size slide

  36. @jnchito
    blog.jnito.com
    'PMMPXNF

    View full-size slide