valibuk.net

Filtering is basically about writing conditions. In most cases the number of conditions is fixed, e.g. an internal filter in an application. Then it is quite easy to prepare a value for the conditions parameter.

But what to do if a number of conditions may vary? It is not a good idea to pass an empty value, because an empty value is still a valid value. We have to somehow implicitly say that a condition with an empty value should not be taken into a filtering process at all.

One option is to create a complicated condition that will distinguish between an empty value and a meaningful value, e.g.

... AND ((? == "") OR (city LIKE ?)) AND ...

or another option is to improve creating of a value of the conditions parameter.

Let’s assume our filter consists of two fields: first_name and last_name. A conditions value is computed in a controller:

conditions_text = []
conditions_params = []

if @params[:first_name]
  conditions_text << "first_name ILIKE ?"
  conditions_params << "%#{@params[:first_name]}%"
end
if @params[:last_name]
  conditions_text << "last_name ILIKE ?"
  conditions_params << "%#{@params[:last_name]}%"
end

conditions = conditions_text.empty? ?
  nil : [conditions_text.join(" and "), *conditions_params]

@total = User.count(:conditions => conditions)
@user_pages, @users = paginate :users,
  :conditions => conditions,
  :per_page => 10

Lines 1-2 initialise two variables - conditions_text for condition definitions and conditions_params for parameters.

Lines 4-7 and 8-11 contain a definition of two parameters. They are added only if a value of the corresponding filter form fields is not empty.

The magic is on lines 13 and 14. If a value of the conditions_text is empty then a nil value is used as the condition, i.e. filtering is not active. Otherwise a value of the condition parameter is constructed — it is a pair definition–parameters.
A definition value, a string, is created by joing all particular definitions with the AND keyword (with two spaces).
A parameters value is created by converting a list to an array.

Lines 16-19 show an example how to use the generated value (I use it for an AJAX filtering).

Conclusion

The proposed solution works only with only-AND or only-OR conditions — what is in most cases enough.
To really solve this, It is possible to have two (AND, OR) sets of definitions and parameters and put them together at the end… but it is quite cumbersome. Is there a better solution?

Thanks to Viktor for help with the *conditions_params code.