Simple multi-tables and multi-types searching with Postgres and Rails < 5

Web users like to be able to search within websites. To create a good web search application, you need fast, efficient searching over multiple tables. From my own experience, I can say that this trivial task can become a real pain, especially when you need to search columns of different types.

Let’s say we have to implement a search in a blog app. We need to be able to find posts by post (title, description and zip code) and user (first name, last name, full name, phone number).

Rails allow extracting query logic from models and controllers; this pattern is called Query Object. Let’s create a new folder /queries under /app, and create a file /queries/posts_query.rb. It’s good practice to stick to the same naming conventions in order to prevent confusion when another developer works on the project in the future.

posts_controller.rb

class PostsController < ApplicationController
  def index
    @posts = PostsQuery.(Post.all, search: params[:search])
  end
end

I suggest passing the posts collection as an argument to query an object. It keeps posts_query decoupled and more flexible.

queries/posts_query.rb

class PostsQuery
  def self.call(relation = Post.all, search:)
    relation
      .joins(:person)
      .where(new(search).search_sql)
      .order(created_at: :desc)
  end

  def initialize(search = nil)
    @search = search
  end

  def search_sql
    return nil if search.blank?
    [user_number_columns_sql, post_number_columns_slq, user_string_columns_sql, post_string_columns_sql].compact.join(' OR ')
  end

  def user_number_columns_sql
    return nil unless search_number?
    "users.phone = #{search}"
  end

  def user_string_columns_sql
    "lower(users.first_name) ilike '%#{prepared_keyword}%' OR
     lower(users.last_name) ilike '%#{prepared_keyword}%' OR
     CONCAT(lower(users.first_name), ' ', lower(users.last_name)) ilike '%#{prepared_keyword}%'"
  end

  def post_number_columns_sql
    return nil unless search_number?
    "posts.zip_code = #{search}"
  end

  def post_string_columns_sql
    "posts.title ilike '%#{prepared_keyword}%' OR
     posts.description ilike '%#{prepared_keyword}%'"
  end

  private

  attr_reader :search
  def prepared_keyword
    search_number? ? search : search.strip.downcase
  end
  
  def search_number?
    true if Float(search) rescue false
  end
end 

The main logic is in this line:

.where(new(search).search_sql)

Here we initialize the instance of a class and perform method search_sql, which prepares SQL queries.

def search_sql
  return nil if search.blank?
  [user_number_columns_sql, post_number_columns_slq, user_string_columns_sql, post_string_columns_sql].compact.join(' OR ')
end

The first line checks to see if the search value is present. If not, it returns nil. In this case, it doesn’t affect to relation, and the result would be an initial collection.

def user_number_columns_sql
  return nil unless search_number?
  "users.phone = #{search}"
end

Ruby can convert a string into a number. If the string contains letters, you will receive zero; otherwise, you’ll receive the number itself. In the first line, we return nil if the result of the conversion is zero. Otherwise, the method returns a string with SQL. That’s how we deal with Number columns.

def user_string_columns_sql
  "lower(users.first_name) ilike '%#{prepared_keyword}%' OR
  lower(users.last_name) ilike '%#{prepared_keyword}%' OR
  CONCAT(lower(users.first_name), ' ', lower(users.last_name)) ilike '%#{prepared_keyword}%'"
end
def prepared_keyword
  search_number? ? search : search.strip.downcase
end

In this method, we prepare the SQL for columns with string type. We use a lower() function to convert letters in the database to lowercase. The prepared_keyword method makes search value lowercase and removes white spaces. The CONCAT() function combines multiple columns into a single one. post_number_columns_slq and post_string_columns_sql are similar methods, but only for posts tables.

def search_sql
  return nil if search.blank?
  [user_number_columns_sql, post_number_columns_slq, user_string_columns_sql, post_string_columns_sql].compact.join(' OR ')
end

Some of the methods in the array will return nil, so we need to use the .compact method to remove all nils. The .join(‘ OR ’) method will add each element with a connector ‘OR’. As a result, we will get this:

search = 'title'
new(search).search_sql
###
"lower(users.first_name) ilike '%title%' OR lower(users.last_name) ilike '%#title%' OR CONCAT(lower(users.first_name), ' ', lower(users.last_name)) ilike '%title%' OR posts.title ilike '%title%' OR posts.description ilike '%title%'"

Conclusion

Almost every website contains a search box. This feature has to be flexible enough to find records from any kind of column. In this article, I provided a simple search functionality that allows you to find records in multiple tables and in columns with different types.

This is very simple search, so it’s too slow to use in applications with thousands of records. For larger applications, you’ll need to add external services like Elasticsearch. We’ll tackle that in the next article.

Happy Coding 🙂