Conditional chaining in a scope

Question

This scope should handle optional keys in the passed hash. When the keys 'organisation' and 'website' exists, both wheres should be chained together.

scope :search_provider, lambda { |search|
  if search['organisation'].present?
    where("name COLLATE utf8_general_ci LIKE ?", "%#{search['organisation']}%")
  end
  if search['website'].present?
    where("website COLLATE utf8_general_ci LIKE ?", "%#{search['website']}%")
  end
}

But unfortunately, this isn't happening:

Provider.search_provider({'website' => 'Hello'}).to_sql
 => "SELECT `provider`.* FROM `providers`  WHERE (website COLLATE utf8_general_ci LIKE '%Hello%')"

[OK]

Provider.search_provider({'organisation' => 'Hello'}).to_sql
 => "SELECT `provider`.* FROM `providers`"

[WRONG]

Provider.search_provider({'website' => 'Hello', 'organisation' => 'huhu'}).to_sql
 => "SELECT `provider`.* FROM `providers`  WHERE (website COLLATE utf8_general_ci LIKE '%Hello%')"

[WRONG]

Only the last where (website) is used and the first where (organisation) is lost. I guess the problem is the missing else case...I tried it with where(nil), but it doesn't worked.

How do I fix it?

Edit:

I can work with a conditional like

if search['organisation'].present? && search['website'].present? 
  ... 
elsif search['organisation'].present?
  ... 
elsif search['website'].present? 
  ...

but I want to insert more keys in the hash and the conditional statement would get quite big with 6 or 7 keys to consider..

score 2 · Accepted Answer · answered Feb 08 '17 at 16:37

You can use scope like this here:

scope :search_provider, ->(search) {
 scope = where('TRUE')
 if search['organisation'].present?
   scope = scope.where({ :some_condition => :some_value })
 end
 if search['website'].present?
   scope = scope.where({ :some_condition => :some_value })
 end
 scope
}

max · Answer 2 · 2017-02-08T16:48:20.260

scope is really just a syntactic sugar for declaring class methods. Don't use scope when it does not fit into a neat little one-liner.

If you declare it as formal class method its much simpler to structure.

def self.search(**kwargs)
  kwargs.compact.each_with_object(self.all) do |(key, value), memo|
    memo.where( "? COLLATE utf8_general_ci LIKE ?", key.to_s, value )
  end
end

You would call it from your controller using #permit or #slice on a hash.

@providers = Provider.search(params.permit(:organisation, :website))

Conditional chaining in a scope

2 Answers2