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"
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
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 http://guides.rubyonrails.org/active_record_querying.html and perhaps i could use the except command here somewhere...but this relationship model just seems a bit complex to do this with ActiveRecord...how may I?, i really think i should use ActiveRecord, but how?
Thank you
You can apply multiple where
calls to a query so you can build your base 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 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)
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 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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With