Marat's Notes

Few articles ago we created a Customer model with custom fields, based on JSONB column in PostgreSQL.

Today, let’s build a field searcher, so you can search on custom fields by case independent value with %LIKE% syntax.

Filterable module

This module should be included into your controller like usual include Filterable.

First of all, we should initialize it with params hash with fields and filter fields.

Let’s say like this: order=asc&page=1&filter=KZN&fields=city.

Example of Filtration class usage in controller:

searcher = Filterable::Filtration.new(params)
default_search = searcher.search_params

Let’s check some source code for Filtration class:

module Filterable
  class Filtration
    
    attr_accessor :search_strings, :fields, :query, :fields_array, :query_array

    def initialize(params)
      @fields = params[:fields]
      @query = params[:filter]
      @fields_array = []
      @query_array = []
      @search_strings = []
    end

    ...

    # FILTER DEFAULT PARAMS AND SEARCHABLE, AND BUILD CORRECT HASH
    def build_hash(fields, filters)
      data = []
      default = Customer::COLUMNS
      h = {}
      fields.zip(filters) { |a,b| h[a.to_sym] = b } 
      h.each do |k, v|
        if (v && v != '') && !default.include?(k.to_s)
          data << { name: k, value: v&.downcase }
        end
      end
      data
    end

    ...

    # CREATE SEARCH SQL STRINGS FOR SEARCHER
    def search_params
      result = ''
      to_delete = []
      if @fields_array && @fields_array.any?
        data = []
        @search_strings.each_with_index do |header, index|
          data << "u.#{header} ilike '%#{query_array[index]}%'"
        end
        result = data.each_with_index.map{ |item, index| index == 1 ? (' and ' + item) : item }.join
      end
      result
    end

    ...
  end
end

Prepare search hash

So, let’s split params by delimeter, and pass them to the build_hash method:

fields = params[:fields].split('|')
filters = params[:filter].split('|')
q = searcher.build_hash(fields, filters)

Searcher

This is main search class, here we are using separate connection to our DB instance to filter data, like this:

data = Searcher.new(current_user.id, q, default_search, page, sort)

The most interesting method in Searcher class called to_sql, we generating SQL statements from different fields in a loop.

In our case, we have jsonb structure like this, so no uniq keys and in same time system should provide ability for searching with multiple conditions using %LIKE% syntax in value fields.

[
  {"name": "location", "value": "KZN"},
  {"name": "ig", "value": "IG"},
  {"name": "age", "value": "24"},
  {"name": "city", "value": "KZN"},
  {"name": "team", "value": "ASR"},
  {"name": "size", "value": "8"},
  {"name": "code", "value": "SSS"}
]

So, this code related to SQL generation with json syntax -»:

sql << "exists (select * from jsonb_array_elements(u.custom_fields) as s(j) "
sql << "where lower(s.j ->> 'value') ilike #{"'%#{item[:value]}%'"} and lower(s.j ->> 'name') in ('#{item[:name]}'))"

Searcher class implementation.

Here we’re passing user_id, search string, page, sort and default limit per page.

We also paginating results manually, with total_items, total_pages, offset.

class Searcher
  def initialize(user_id, q, default_search, page, sort, limit = 20)
    @page = page.to_i
    @limit = limit
    @q = q
    @user_id = user_id
    @default_search = default_search
    @sort = sort
  end

  def current_page
    @page
  end

  def total_items
    res = execute(to_count_sql)
    total = res.getvalue(0, 0)
    total.to_f.ceil
  end

  def total_pages
    (total_items / @limit).ceil
  end

  def limit_value
    @limit
  end

  def offset
    (@page - 1) * @limit
  end

  def all
    execute(to_sql)
  end

  def execute(sql)
    config = ActiveRecord::Base.configurations[Rails.env] || Rails.application.config.database_configuration[Rails.env]
    host     = config["host"]
    port     = config["port"]
    database = config["database"]
    username = config["username"]
    password = config["password"]
    begin
      con = PG.connect(host, port, '', '', database, username, password)
      res = con.exec(sql)
    rescue PG::Error => e
      Rails.logger.error(e.message)
    ensure
      con.close if con
    end
    res
  end

  def to_sql
    sql = "select u.*
    from customers AS u where "
    if @q.any?
      @q.each_with_index do |item, index|
        sql << ' and ' if index != 0
        sql << "exists (select * from jsonb_array_elements(u.custom_fields) as s(j) "
        sql << "where lower(s.j ->> 'value') ilike #{"'%#{item[:value]}%'"} and lower(s.j ->> 'name') in ('#{item[:name]}'))"
      end
    end

    if @default_search && @default_search.present?
      if @q.any?
        sql << ' and ' + @default_search
      else
        sql << @default_search
      end
    end

    sql << " and user_id=#{@user_id}"
    sql << " order by #{@sort} "
    sql << " limit #{@limit} offset #{offset}"
    Rails.logger.info(sql)
    sql
  end
  ...
end

Final steps

So, we are getting IDs from Searcher class and just passing them to the Customer model to retrieve data:

data = Searcher.new(current_user.id, q, default_search, page, sort)
results = Customer.where(id: data.all.map { |x| x['id']&.to_i })

Custom queries on JSONB column

Filterable module

Prepare search hash

Searcher

Final steps