CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-4
muichkine
muichkine

Reputation: 2940

ActiveRecord build dynamic query conditions

I have a piece of code that seems to work. I just think there may be a much better way to achieve the desired work. The problem is to build an ActiveRecord query with an unknown list of parameters.

Here is the code:

query_string = String.new
query_values = []
unless params[:organization][:name].blank?
  query_string << 'name = ?'
  query_values << params[:organization][:name]
end
unless params[:organization][:national_id].blank? && params[:organization][:vat_id].blank?
  raise RequestParamsException.new('National ID or Vat ID given without country') if params[:organization][:country].nil?
  country_id = Country.find_by_name(params[:organization][:country]).pluck(:id)
  unless params[:organization][:national_id].blank?
    query_string << ' OR ' unless query_string.empty?
    query_string << '(national_id = ?'
    query_values << params[:organization][:national_id]
    query_string << ' AND ' << 'country_id = ?)'
    query_values << country_id
  end
  unless params[:organization][:vat_id].blank?
    query_string << ' OR ' unless query_string.empty?
    query_string << '(vat_id = ?'
    query_values << params[:organization][:vat_id]
    query_string << ' AND ' << 'country_id = ?)'
    query_values << country_id
  end
end
known_organizations = query_string.blank? ? [] : Organization.where(query_string, query_values).uniq

Country is needed when a Vat or National Id are given since these are scoped in the model:

class Organization < ActiveRecord::Base    

#======================VALIDATIONS=========================
  validates :national_id, :uniqueness => { :scope => :country_id }, :allow_blank => true
  validates :vat_id, :uniqueness => { :scope => :country_id }, :allow_blank => true
  validates :country, :presence => true  
end

Upvotes: 0

Views: 2164

Answers (1)

Adrian Serafin
Adrian Serafin

Reputation: 7725

You can take advantage of Arel. For example when you write:

posts = Post.where(author_id: 12)

this query isn't executed unless you start iterating on posts or call posts.all. So you can write something like this:

def search_posts
  posts = Post.where(active: true)
  posts = posts.where('body ilike ?', "%#{params[:query]%") unless params[:query].blank? 

  posts
end

This simple example shows how to achieve behavior you are looking for.

Upvotes: 3

Related Questions