How to add conditional where clauses in rails

Question

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

[here is a good example of how to do joins with active record][1] [1]: http://stackoverflow.com/questions/764538/ruby-on-rails-how-to-join-two-tables — Frank Visaggio, Jul 27 '12 at 15:05
Hi, my issue is not with the joins...I just didn't write them because they aren't relevant. My thing is with the conditional search parameters — MrWater, Jul 27 '12 at 15:13
@itsalltime - I think what Bob is trying to say is - why not use Active Record instead of writing straight-up SQL? http://guides.rubyonrails.org/active_record_querying.html — JasCav, Jul 27 '12 at 15:16

score 21 · Accepted Answer · answered Jul 27 '12 at 18:24

21

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.

answered Jul 27 '12 at 18:24

mu is too short

426,620
70
833
800

Hi, this answer is great. Thanks. It seems I can't use the begin_date .. end_date (and had to use blank? instead of nil), because i'm collecting the datetimes from text field and converting them locally. `if !begin_date.blank? begin_date = DateTime.strptime("#{begin_date} 00:00:00", "%m/%d/%Y %H:%M:%S").to_datetime end` I ended up using your alternative suggestion : `or where('created_at between :begin_date and :end_date', :begin_date => begin_date, :end_date => end_date)` – MrWater Jul 28 '12 at 17:08
@itsalltime: Using `begin_date .. end_date` should work if `begin_date` and `end_date` are real date objects but it seems like you actually have strings instead. I'd recommend converting them to date objects before using them so that you only have to worry about date format issues in one place. – mu is too short Jul 28 '12 at 18:53
Hi, I do convert them with the function posted above, but even so the begin_date .. end_date doesn't work, and that's why i had to go for the alternative – MrWater Jul 29 '12 at 22:06
I guess it must have been some other error, since it seems ok now. My problem now is that the "created_at" is being applied to the users table, and i wanted it applied to containers... How might i do that? – MrWater Jul 30 '12 at 09:55

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

I cant think of a great reason why you would actually want to generate SQL in your code. Active record seems like a much more efficient solution for your needs, unless there is a reason why you cant use that.

Link explaining how to join tables with active record

edited May 23 '17 at 11:45

Community

1
1

answered Jul 27 '12 at 15:34

Frank Visaggio

3,642
9
34
71

I actually had some doubts on how to use joins, but i guess i found a way to address what i wanted as i posted in my edited answer. My thing is actually with conditional search parameters. – MrWater Jul 27 '12 at 15:49
oh, for cases like this and stuff: http://www.rigelgroupllc.com/blog/2014/09/14/working-with-complex-sql-statements/ – Danny Feb 27 '15 at 16:36

How to add conditional where clauses in rails

2 Answers2

Linked

Related