Whereable Query Pattern. A design pattern for combining numerous types of queries only if the query is relevant into a single query using ARel

searches_stuff.rb

# I heard Whereables were having a moment.
#
# More on the real version of this feature here:
#   https://community.wanikani.com/t/kamesame-a-fast-feature-rich-japanese-memorization-webapp/31319/1575?u=searls
#
# Below is simplified but basic usage:
#
#   SearchesStuff.new.call("arigatou") # finds ありがとう
#   SearchesStuff.new.call("にほんご") # finds 日本語
#   SearchesStuff.new.call("探して") # finds 探す
#   SearchesStuff.new.call("contxmporary") # finds コンテンポラリー
class SearchesStuff
  Whereable = Struct.new(
    :valid,
    :where,
    :rank,
    keyword_init: true
  )

  def initialize
    @massages_query = MassagesQuery.new
  end

  def call(query, user, limit: 100)
    query = @massages_query.call(query)

    whereables = [
      Whereable.new(
        valid: query.text_template.present?,
        where: Item.where("texts_tsvector @@ (#{query.text_template})", *query.text_values),
        rank: sanitize("ts_rank(texts_tsvector, (#{query.text_template}))", *query.text_values)
      ),
      Whereable.new(
        valid: query.reading_template.present?,
        where: Item.where("reading_texts_tsvector @@ (#{query.reading_template})", *query.reading_values),
        rank: sanitize("ts_rank(reading_texts_tsvector, (#{query.reading_template}))", *query.reading_values)
      ),
      Whereable.new(
        valid: query.english_websearch.present?,
        where: Item.where("to_tsvector('english', meaning_text) @@ websearch_to_tsquery('english', ?)", query.english_websearch),
        rank: sanitize("ts_rank(to_tsvector('english', meaning_text), websearch_to_tsquery('english', ?))", query.english_websearch)
      ),

      # Search official and user custom english definitions
      Whereable.new(
        valid: query.english_websearch.present? && user.present? && (
          definitions = Definition
            .select(:item_id, sanitize("ts_rank(to_tsvector('english', text), websearch_to_tsquery('english', ?)) tsrank", query.english_websearch))
            .where("official or user_id = ?", user.id)
            .where("to_tsvector('english', text) @@ websearch_to_tsquery('english', ?)", query.english_websearch)
            .order(Arel.sql(sanitize("ts_rank(to_tsvector('english', text), websearch_to_tsquery('english', ?))", query.english_websearch)))
            .limit(limit)
        ).present?,
        where: definitions.present? ? Item.where(id: definitions.map(&:item_id)) : Item.where("false"),
        rank: definitions.present? ? sanitize("case #{definitions.map { |d| "when items.id = ? then ?" }.join(" ")} end", *definitions.flat_map { |d| [d.item_id, d.tsrank] }) : "0"
      ),

      # Search official and user custom japanese spellings
      Whereable.new(
        valid: query.text_queries.present? && user.present? && (
          spellings = Spelling
            .select(:item_id)
            .where("official or user_id = ?", user.id)
            .where(text: query.text_queries)
            .limit(limit)
        ).present?,
        where: spellings.present? ? Item.where(id: spellings.map(&:item_id)) : Item.where("false"),
        rank: spellings.present? ? sanitize("case #{spellings.map { |d| "when items.id = ? then ?" }.join(" ")} end", *spellings.flat_map { |d| [d.item_id, 1] }) : "0"
      )
    ].select(&:valid)

    if whereables.present?
      Item.merge(whereables.map(&:where).reduce { |arel, where|
        arel.or(where)
      }).order(Arel.sql("(greatest(#{whereables.map(&:rank).join(", ")}) desc"))
        .limit(limit)
    else
      []
    end
  end

  private

  def sanitize(sql, *values)
    ActiveRecord::Base.sanitize_sql_for_conditions([sql, *values])
  end
end