Building queries dynamically in rails

Question

Im trying to replicate the searching list style of crunchbase using ruby on rails. I have an array of filters that looks something like this:

[
   {
      "id":"0",
      "className":"Company",
      "field":"name",
      "operator":"starts with",
      "val":"a"
   },
   {
      "id":"1",
      "className":"Company",
      "field":"hq_city",
      "operator":"equals",
      "val":"Karachi"
   },
   {
      "id":"2",
      "className":"Category",
      "field":"name",
      "operator":"does not include",
      "val":"ECommerce"
   }
]

I send this json string to my ruby controller where I have implemented this logic:

filters = params[:q]
table_names = {}
filters.each do |filter|
    filter = filters[filter]
    className = filter["className"]
    fieldName = filter["field"]
    operator = filter["operator"]
    val = filter["val"]
    if table_names[className].blank? 
        table_names[className] = []
    end
    table_names[className].push({
        fieldName: fieldName,
        operator: operator,
        val: val
    })
end

table_names.each do |k, v|
    i = 0
    where_string = ''
    val_hash = {}
    v.each do |field|
        if i > 0
            where_string += ' AND '
        end
        where_string += "#{field[:fieldName]} = :#{field[:fieldName]}"
        val_hash[field[:fieldName].to_sym] = field[:val]
        i += 1
    end
    className = k.constantize
    puts className.where(where_string, val_hash)
end

What I do is, I loop over the json array and create a hash with keys as table names and values are the array with the name of the column, the operator and the value to apply that operator on. So I would have something like this after the table_names hash is created:

{
   'Company':[
      {
         fieldName:'name',
         operator:'starts with',
         val:'a'
      },
      {
         fieldName:'hq_city',
         operator:'equals',
         val:'karachi'
      }
   ],
   'Category':[
      {
         fieldName:'name',
         operator:'does not include',
         val:'ECommerce'
      }
   ]
}

Now I loop over the table_names hash and create a where query using the Model.where("column_name = :column_name", {column_name: 'abcd'}) syntax.

So I would be generating two queries:

SELECT "companies".* FROM "companies" WHERE (name = 'a' AND hq_city = 'b')
SELECT "categories".* FROM "categories" WHERE (name = 'c')

I have two problems now:

1. Operators:

I have many operators that can be applied on a column like 'starts with', 'ends with', 'equals', 'does not equals', 'includes', 'does not includes', 'greater than', 'less than'. I am guessing the best way would be to do a switch case on the operator and use the appropriate symbol while building the where string. So for example, if the operator is 'starts with', i'd do something like where_string += "#{field[:fieldName]} like %:#{field[:fieldName]}" and likewise for others.

So is this approach correct and is this type of wildcard syntax allowed in this kind of .where?

2. More than 1 table

As you saw, my approach builds 2 queries for more than 2 tables. I do not need 2 queries, I need the category name to be in the same query where the category belongs to the company.

Now what I want to do is I need to create a query like this:

Company.joins(:categories).where("name = :name and hq_city = :hq_city and categories.name = :categories[name]", {name: 'a', hq_city: 'Karachi', categories: {name: 'ECommerce'}})

But this is not it. The search can become very very complex. For example:

A Company has many FundingRound. FundingRound can have many Investment and Investment can have many IndividualInvestor. So I can select create a filter like:

{
  "id":"0",
  "className":"IndividualInvestor",
  "field":"first_name",
  "operator":"starts with",
  "val":"za"
} 

My approach would create a query like this:

SELECT "individual_investors".* FROM "individual_investors" WHERE (first_name like %za%)

This query is wrong. I want to query the individual investors of the investments of the funding round of the company. Which is a lot of joining tables.

The approach that I have used is applicable to a single model and cannot solve the problem that I stated above.

How would I solve this problem?

Answer 1

I would suggest altering your JSON data. Right now you only send name of the model, without the context, it would be easier if your model would have context.

In your example data would have to look like

data = [
  {
    id: '0',
    className: 'Company',
    relation: 'Company',
    field: 'name',
    operator: 'starts with',
    val: 'a'
  },
  {
    id: '1',
    className: 'Category',
    relation: 'Company.categories',
    field: 'name',
    operator: 'equals',
    val: '12'
  },  
  {
    id: '3',
    className: 'IndividualInvestor',
    relation:     'Company.founding_rounds.investments.individual_investors',
    field: 'name',
    operator: 'equals',
    val: '12'
  }
]

And you send this data to QueryBuilder

query = QueryBuilder.new(data) results = query.find_records

Note: find_records returns array of hashes per model on which you execute query.

For example it would return [{Company: [....]]

class QueryBuilder
  def initialize(data)
    @data = prepare_data(data)
  end

  def find_records
    queries = @data.group_by {|e| e[:model]}
    queries.map do |k, v|
      q = v.map do |f|
        {
          field: "#{f[:table_name]}.#{f[:field]} #{read_operator(f[:operator])} ?",
          value: value_based_on_operator(f[:val], f[:operator])
        }
      end

      db_query = q.map {|e| e[:field]}.join(" AND ")
      values = q.map {|e| e[:value]}

      {"#{k}": k.constantize.joins(join_hash(v)).where(db_query, *values)}
    end
  end

  private

  def join_hash(array_of_relations)
    hash = {}
    array_of_relations.each do |f|
      hash.merge!(array_to_hash(f[:joins]))
    end
    hash.map do |k, v|
      if v.nil?
        k
      else
        {"#{k}": v}
      end
    end
  end

  def read_operator(operator)
    case operator
    when 'equals'
      '='
    when 'starts with'
      'LIKE'
    end
  end

  def value_based_on_operator(value, operator)
    case operator
    when 'equals'
      value
    when 'starts with'
      "%#{value}"
    end
  end

  def prepare_data(data)
    data.each do |record|
      record.tap do |f|
        f[:model] = f[:relation].split('.')[0]
        f[:joins] = f[:relation].split('.').drop(1)
        f[:table_name] = f[:className].constantize.table_name
      end
    end
  end

  def array_to_hash(array)
    if array.length < 1
      {}
    elsif array.length == 1
      {"#{array[0]}": nil}
    elsif array.length == 2
      {"#{array[0]}": array[1]}
    else
      {"#{array[0]}": array_to_hash(array.drop(1))}
    end
  end
end