title: Building queries dynamically in rails
body: <p>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:</p>
  
  <pre><code>[
     {
        "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"
     }
  ]
  </code></pre>
  
  <p>I send this json string to my ruby controller where I have implemented this logic:</p>
  
  <pre><code>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 &gt; 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
  </code></pre>
  
  <p>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 <code>table_names</code> hash is created:</p>
  
  <pre><code>{
     'Company':[
        {
           fieldName:'name',
           operator:'starts with',
           val:'a'
        },
        {
           fieldName:'hq_city',
           operator:'equals',
           val:'karachi'
        }
     ],
     'Category':[
        {
           fieldName:'name',
           operator:'does not include',
           val:'ECommerce'
        }
     ]
  }
  </code></pre>
  
  <p>Now I loop over the table_names hash and create a where query using the <code>Model.where("column_name = :column_name", {column_name: 'abcd'})</code> syntax.</p>
  
  <p>So I would be generating two queries:</p>
  
  <pre><code>SELECT "companies".* FROM "companies" WHERE (name = 'a' AND hq_city = 'b')
  SELECT "categories".* FROM "categories" WHERE (name = 'c')
  </code></pre>
  
  <p>I have two problems now:</p>
  
  <p><strong>1. Operators:</strong></p>
  
  <p>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 <code>where_string += "#{field[:fieldName]} like %:#{field[:fieldName]}"</code> and likewise for others. </p>
  
  <p>So is this approach correct and is this type of wildcard syntax allowed in this kind of <code>.where</code>?</p>
  
  <p><strong>2. More than 1 table</strong></p>
  
  <p>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.</p>
  
  <p>Now what I want to do is I need to create a query like this:</p>
  
  <pre><code>Company.joins(:categories).where("name = :name and hq_city = :hq_city and categories.name = :categories[name]", {name: 'a', hq_city: 'Karachi', categories: {name: 'ECommerce'}})
  </code></pre>
  
  <p>But this is not it. The search can become very very complex. For example:</p>
  
  <p>A Company has many FundingRound. FundingRound can have many Investment and Investment can have many IndividualInvestor. So I can select create a filter like:</p>
  
  <pre><code>{
    "id":"0",
    "className":"IndividualInvestor",
    "field":"first_name",
    "operator":"starts with",
    "val":"za"
  } 
  </code></pre>
  
  <p>My approach would create a query like this:</p>
  
  <pre><code>SELECT "individual_investors".* FROM "individual_investors" WHERE (first_name like %za%)
  </code></pre>
  
  <p>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.</p>
  
  <p>The approach that I have used is applicable to a single model and cannot solve the problem that I stated above.</p>
  
  <p>How would I solve this problem?</p>