Railsでアプリケーションコードで利用する生SQLを書くときにやっていること

TL;DR

  • SQLは別にそのSQLを実行するためのClassを作ってそこで書いたり色々やっている。
  • パラメータのバリデーションもそのClassでやってる。
    • 実装自体はActiveModelのValidateでサクッと実装。
  • select_allの結果は基本的にはHashなので、Structなどでオブジェクトっぽく振る舞えるようにしている。
  • BaseQueryみたいなコード書いておくと便利
    • ページネーションが必要なときも型はだいたい決まっているので、ベースClassに書いておくと便利そう。

具体例

複数のTableの内容LIKE検索したものをUnionで結合したクエリを発行するような Search クラスを実装してみることこんな感じ。 今回はMySQLを想定して実装してみました。

Controllerからはこんな感じで呼び出せるものとして実装しています。

※あくまで実装のご提案で流れがわかればいいと思ったので、 このコードを実際に動かして確認はしてないです。(業務コードではだいたい同じ感じで動いてますが。)

class SearchController < ApplicationController
    def index
        params[:page] ||= 1
        params[:limit] ||= 30
        @search = Search.execute(params)
        if @search.success?
            render 'index.json'
        else 
            render json: @search.errors, status: 422
        end
    end
end
class Search
  include ActiveModel::Model
  include ActiveRecord::Sanitization::ClassMethods

  attr_reader :result, :page, :limit, :order, :keyword, :context, :total_count

  validates :keyword, presence: true
  validates :context, inclusion: { in: %w(all staff user) }
  validates :order, inclusion: { in: %w(desc asc) }

  def self.execute(params)
    new(params).execute
  end

  def execute()
    if valid?
      con = ActiveRecord::Base.connection

      @search_keyword = sanitize_sql_like(keyword)
      @total_count = con.select_value(total_count_sql)
      @result = con.select_all(search_sql)
      assign_objects()
    end
    self
  end

  def initialize(params)
    @page = params[:page].to_i
    @limit = params[:limit].to_i
    @order = params[:order]
    @keyword = params[:keyword]
    @context = params[:context]

    @result = nil
    @total_count = 0
  end

  def success?
    result.present? && errors.empty?
  end

  def last_page?
    return true if total_count == 0
    (total_count.to_f / (limit * page)).ceil <= 1
  end

  private

  attr_reader :search_keyword

  def search_sql
sql_text =<<EOF
  SELECT s.* FROM (#{base_sql}) s #{order_sql} #{pagenate_sql}
EOF
  end

  def total_count_sql
sql_text =<<EOF
  SELECT COUNT(*) FROM (#{base_sql}) s #{order_sql}
EOF
  end

  def base_sql
    case context
    when "all"
      [staff_sql, user_sql].join("UNION\n ")
    when "staff", "user"
      send("#{context}_sql")
    else
      raise TypeError 'Not Found Context'
    end
  end

  def user_sql
sql_text = <<EOF
SELECT 
     user.id          AS id,
     user.name        AS name,
     user.image_url   AS image_url,
     user.created_at  AS created_at,
     user.updated_at  AS updated_at
FROM   users AS user
WHERE  user.name LIKE '%#{search_keyword}%'
EOF
    sql_text
  end

  def staff_sql
sql_text = <<EOF
SELECT 
     staff.id          AS id,
     staff.name        AS name,
     staff.image_url   AS image_url,
     staff.created_at  AS created_at,
     staff.updated_at  AS updated_at
FROM   staffs AS staff
WHERE  staff.name LIKE '%#{search_keyword}%'
EOF
    sql_text
  end

  def order_sql
    "ORDER BY s.created_at #{order.upcase}"
  end

  def pagenate_sql
    "LIMIT #{limit} OFFSET #{offset};"
  end

  def offset
    (page - 1) * limit
  end
 
  def assign_objects()
    SearchObj = Struct.new(:id, :name, :image_url, :created_at, :updated_at)
    @result = @result.map { |r| SearchObj.new(n['id'], n['name'], n['image_url'], n['created_at'], n['updated_at']) }
  end
end
#.jbuilder
json.result (@search.result) do |result|
  json.extract! result, :id, :image_url, :name, :created_at, :updated_at
end

json.page @search.page
json.limit @search.limit
json.order @search.order
json.keyword @search.keyword
json.context @search.context
json.total_count @search.total_count
json.is_last_page @search.last_page?

この設計自体の工夫点

呼び出しメソッドのルールを決める

  • これが一番なによりも肝だと思っていて、この生SQL用のクラスを作っておくことで、Controllerに生SQLを書くことを防げるし、テストも実装の置き換えを容易にしているところがこの実装の気に入ってるところです。
  • 自分のケースではそこまでこのクラスを多用することがないので、雑に modelsに放り込んでいます。
  • 呼び出しのメソッドについては、今回のところは#execute.executeにしてますが、#call#queryにしてもいいかもしれません。

ActiveModelでサクッとパラメータバリデーション

  • errosって変数が生えてバリデーションエラーの内容を詰めてくれるし、簡単にバリデーションの定義ができてとにかく便利。ここでは、バリデーションエラーの場合はクエリを実行しないようにしています。

ハッシュ地獄からの脱却

select_allの内容は厳密には異なりますがまぁHashです。

これはRubyを使っている以上なんかイケてないのと、要素のTypoに気づけない問題があるので、resultの内容は、Object(っぽいもの)にしたいですね。 ちょっと大きめのものであればDecoratorクラスを作っていいですが、比較的シンプルなので今回は簡単にStructで実装してみました。

Decoratorクラスについてはこの辺を参照するといいと思います。 morizyun.github.io

もうちょっと便利にしてみる

ここからはこの記事を書いていて思いついたのですが、 もうちょっとだけこのクラスをブラッシュアップできそうです。

ベースクラスを作る

ページネーション関連のメソッドは、特殊な要件がなければこの実装方法で基本的には問題にならないでしょう。 なので、ページネーションと必ず利用する(であろう)パラメータに関しては共通化してしまいましょう。

今回の場合は適当に、 SearchQueryBase とかにしておきましょうかね。 今回は継承をベースにしていますが、ModuleでどうにかするのがRubyっぽいかもしれません。ただ superとか使っているので、自明かと思って今回は継承にしてみました。

class SearchQueryBase

  include ActiveModel::Model
  include ActiveRecord::Sanitization::ClassMethods

  attr_reader :result, :page, :limit, :order, :total_count

  def self.execute(params)
    new(params).execute
  end

  def execute(params)
     raise NotImplementError
   end

  def initialize(params)
    @page = params[:page].to_i
    @limit = params[:limit].to_i
    @order = params[:order]
    @result = nil
    @total_count = 0
  end

  def success?
    result.present? && errors.empty?
  end

  def last_page?
    return true if total_count == 0
    (total_count.to_f / (limit * page)).ceil <= 1
  end

  private

  def pagenate_sql
    "LIMIT #{limit} OFFSET #{offset};"
  end

  def offset
    (page - 1) * limit
  end
end

class Search < SearchQueryBase

attr_reader :result, :page, :limit, :order, :keyword, :context, :total_count

  validates :keyword, presence: true
  validates :context, inclusion: { in: %w(all staff user) }
  validates :order, inclusion: { in: %w(desc asc) }

  def execute()
    if valid?
      con = ActiveRecord::Base.connection

      @search_keyword = sanitize_sql_like(keyword)
      @total_count = con.select_value(total_count_sql)
      @result = con.select_all(search_sql)
      assign_objects()
    end
    self
  end

  def initialize(params)
    super
    @keyword = params[:keyword]
    @context = params[:context]
  end

  private

  attr_reader :search_keyword

  def search_sql
sql_text =<<EOF
  SELECT s.* FROM (#{base_sql}) s #{order_sql} #{pagenate_sql}
EOF
  end

  def total_count_sql
sql_text =<<EOF
  SELECT COUNT(*) FROM (#{base_sql}) s #{order_sql}
EOF
  end

  def base_sql
    case context
    when "all"
      [staff_sql, user_sql].join("UNION\n ")
    when "staff", "user"
      send("#{context}_sql")
    else
      raise TypeError 'Not Found Context'
    end
  end

  def user_sql
sql_text = <<EOF
SELECT 
     user.id          AS id,
     user.name        AS name,
     user.image_url   AS image_url,
     user.created_at  AS created_at,
     user.updated_at  AS updated_at
FROM   users AS user
WHERE  user.name LIKE '%#{search_keyword}%'
EOF
    sql_text
  end

  def staff_sql
sql_text = <<EOF
SELECT 
     staff.id          AS id,
     staff.name        AS name,
     staff.image_url   AS image_url,
     staff.created_at  AS created_at,
     staff.updated_at  AS updated_at
FROM   staffs AS staff
WHERE  staff.name LIKE '%#{search_keyword}%'
EOF
    sql_text
  end

  def order_sql
    "ORDER BY s.created_at #{order.upcase}"
  end

  def assign_objects()
    SearchObj = Struct.new(:id, :name, :image_url, :created_at, :updated_at)
    @result = @result.map { |r| SearchObj.new(n['id'], n['name'], n['image_url'], n['created_at'], n['updated_at']) }
  end

end

少しだけスッキリしました。 継承したので、Rubyらしくはないですが#executeをInterfaceっぽい感じで、必ず実装しておくべき項目かを自明にしておきました。 これで似たような生SQLを書く際にルールを統一できて良さそうです。

ただ、SQLRubyのコードももうちょっと洗練の余地がありそうな気がします。 例だとSearchControllerでparamsの初期設定しているけど Searchクラス自体にも設定するべきかとか、resultのnilとempty状態でsuccess?の結果が異なるとかかなぁ… 誰かにレビューしてもらってもうちょっと良くしたいな…

そもそも分析用途でなく、幾分シンプルなら生SQLでなくArel使えばいいのでは?

  • ArelはあくまでRailsのプライベートAPIであり、Railsコミュニティから、アプリケーションコードで使うものではないとアナウンスされているから。

テストについて

  • そもそも生SQLを扱うときはテストがないと後からメンテができなくて本当に辛いので、なるべく書くようにしています。
  • 今回自分の使ったケースでは、規模が拡大した段階でElasticSearchなどへの置き換えを検討しているので、Searchクラスへのテストはあまり書かないで、使っているAPIのエンドポイントへのRequest Specに少し厚めにテストを書きました。
  • ElasitcSearchに置き換えした後も共通のインターフェイスで扱えると良いかなと思っています。

終わりに

自分はこんな感じでやっていますということを書いてみました。 みなさんどんな感じでやっているのでしょうか…? 機会があればおききしたいところです。