Postgresql Wildcard Search

user.rb

class User
  
  # This is how I see most wildcard searches done. It matches anywhere, including
  # in the middle of a word. E.g. for "John Doe"
  #   "jo" => yes
  #   "do" => yes
  #   "oe" => yes
  #
  def self.search_anywhere(query)
    where("LOWER(users.first_name) LIKE :query OR LOWER(users.last_name) LIKE :query OR LOWER(users.email) LIKE :query", query: "%#{query}.downcase%")
  end
  
  # This is a little neater, IMHO
  def self.search_anywhere_neater(query)
    where(%w[first_name last_name email].map {|c| "LOWER(users.#{c}) LIKE :query" }.join(" OR "), query: "%#{query.downcase}%")
  end
  
  # This one copes better with word boundaries (depending what behaviour you
  # want). E.g. for "John Doe"
  #   "jo" => yes
  #   "do" => yes
  #   "oe" => no
  #
  def self.search_start_of_words(query)
    # The first space is the delimiter, the second adds a space to the start of the 
    # concatenated string. Note the space inserted in the :search parameter to 
    # only search starts of words.
    where("LOWER(CONCAT_WS(' ', ' ', #{%w[first_name last_name email].join(", ")})) LIKE :search", search: "% #{query.downcase}%")
  end
  
end