How do I conditionally include arguments in a finder where clause?

Question

I'm using Rails 5 with PostGres 9.5. How do I conditionally include arguments in a finder where clause? I have the below

  def self.find_by_name_and_hometown_and_age(user)
    search_criteria = ["my_objects.user_id is null"]
    # Name criteria
    search_criteria.push("upper(my_object_times.name) = ?")

    # Hometown criteria
    search_criteria.push( "upper(my_object_times.city) = ?" ) 
    if user.address.state_id 
      search_criteria.push( "my_object_times.state_id = ?" ) 
    end
    if user.address.country_id
      search_criteria.push( "my_object_times.country_id = ?" ) 
    end

    # Age
    search_criteria.push( "date ? + interval '1 year' * my_object_times.age between my_objects.day - interval '1 year' and my_objects.day + interval '1 year'" ) 

    results = MyObjectTime.joins(:my_object,
                              "LEFT JOIN user_my_object_time_matches on my_object_times.id = user_my_object_time_matches.my_object_time_id").where( search_criteria.join(' and '),
                                                                                                                                "#{user.first_name.upcase} #{user.last_name.upcase}",
                                                                                                                                "#{user.address.city}",
                                                                                                                                "#{user.address.state_id}",
                                                                                                                                "#{user.address.country_id}",
                                                                                                                                user.dob ) 
  end

Note that if there is no "user.address.state_id" field or "user.address.country_id" field, I don't include the criteria in the search, but I'm unclear about how to tell the where clause to ignore those arguments. I would prefer to only have a single where clause.

score 0 · Answer 1 · answered Jan 20 '17 at 18:49

0

where clauses are chainable so you just need simple if statements

all_items = MyObjectTime.all

if some_condition
  all_items = all_items.where(...)
end

if some_other_condition
  all_items = all_items.where(...)
end

And in the end all_items will include what you want.

answered Jan 20 '17 at 18:49

Eyeslandic

14,553
13
41
54

Does the "MyObjectTime.all" execute a query that retrieves all the "MyObjectTime" objects from the database? – Dave Jan 20 '17 at 19:37
If it stands on its own yes, but not when you have chained multiple where clauses to it. The db isn't called until something, usually the view, tries to access the `all_items` variable. So no, it's not all loaded into Rails to start with. – Eyeslandic Jan 20 '17 at 19:45
Oh cool so if both conditions in your example turn out to be true, would it be the equivalent of an AND in the two where clauses? – Dave Jan 20 '17 at 20:58
Yess, it would. – Eyeslandic Jan 20 '17 at 22:35

How do I conditionally include arguments in a finder where clause?

1 Answers1