MrWater MrWater - 1 year ago 79
SQL Question

How to add conditional where clauses in rails

I am a rails newbie and am trying to perform a search on a table with rails, and i'm just using my sql knowledge to do this. But this just doesn't seems like rails or ruby even...

Is there any better way to do what i'm doing below? (basically, only pass date arguments to sql if they are filled)

def search(begin_date=nil, end_date=nil)

subject = " and created_at "

if !(begin_date.nil? || end_date.nil?)
where_part = subject + "BETWEEN :begin_date AND :end_date"
else if (begin_date.nil? && end_date.nil?)
where_part = ""
else if(begin_date.nil?)
where_part = subject + " <= :end_date"
else if (end_date.nil?)
where_part = subject + " >= :begin_date"

User.joins(places: {containers: {label: :user}}).where(" :user_id "+where_part, user_id:, begin_date:begin_date, end_date:end_date).group(...).select(...)



has_many :containers
has_many :user_places
has_many :places, through: :user_places
has_many :labels


has_many :containers
has_many :user_places
has_many :users, through: :user_places


belongs_to :label
belongs_to :place
belongs_to :user


belongs_to :user
has_many :containers

Basically, i want to get a count of the number of containers within a given user's labels or with a direct relationship, per location, and want to be able to filter it by begin and end dates.

Either of this dates may be nil, and so i would need to address this in my "query".

My question is : How can i do this the rails way? I took a look at and perhaps i could use the except command here somewhere...but this relationship model just seems a bit complex to do this with may I?, i really think i should use ActiveRecord, but how?

Thank you

Answer Source

You can apply multiple where calls to a query so you can build your base query:

query = User.joins(...)
            .where(' = :user_id', :user_id =>

and then add another where call depending on your date interval:

if(begin_date && end_date)
  query = query.where(:created_at => begin_date .. end_date)
  # or where('created_at between :begin_date and :end_date', :begin_date => begin_date, :end_date => end_date)
  query = query.where('created_at >= :begin_date', :begin_date => begin_date)
  query = query.where('created_at <= :end_date', :end_date => end_date)

Each where call adds another piece to your overall WHERE clause using AND so something like:

q = M.where(a).where(b).where(c)

is the same as saying WHERE a AND b AND c.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download