How to add conditional conditions where offers in rails

Question

I am new to rails and am trying to do a search on a table with rails and I just use my sql knowledge to do this. But it just doesn't look like rails or even a ruby ​​...

Is there a better way to do what I'm doing below? (basically, only transfer date arguments for sql, if 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" end end end end User.joins(places: {containers: {label: :user}}).where("users.id= :user_id "+where_part, user_id: self.id, begin_date:begin_date, end_date:end_date).group(...).select(...) end 

EDIT

user.rb

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

place.rb

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

container.rb

 belongs_to :label belongs_to :place belongs_to :user 

label.rb

 belongs_to :user has_many :containers 

In principle, I want to get the number of containers within the specified user labels or with direct relationships for each location and I want to be able to filter it by start and end dates.

Any of these dates can be zero, so I will need to refer to this in my "request".

My question is: how can I do this? I took a look at http://guides.rubyonrails.org/active_record_querying.html and maybe I could use the except command here, but this relationship model seems a bit complicated to do it with ActiveRecord ... how can I ?, I really think that should use ActiveRecord, but how?

thanks

Answer 1

You can apply several where calls to the query so that you can create your basic query:

 query = User.joins(...) .group(...) .select(...) .where('users.id = :user_id', :user_id => self.id) 

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

 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) elsif(begin_date) query = query.where('created_at >= :begin_date', :begin_date => begin_date) elsif(end_date) query = query.where('created_at <= :end_date', :end_date => end_date) end 

Each where call adds one more part to your general WHERE clause using AND, and therefore something like:

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

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

Answer 2

I can't think of why you really want to generate SQL in your code. Active recording seems to be a much more effective solution for your needs, unless there is a reason why you cannot use it.

Link explaining how to join tables with an active record