Advanced search/filter use cases


#1

I’ve been working on an application lately that has a lot of advanced
search and filtering functionality (with ranges and substring searches
for each attribute). Despite being a pretty common use case, this isn’t
supported particularly well by any framework I’ve used, including Rails.

I’d like to fix that. :slight_smile:

Right now, my major pet peeves are:

  1. Long, ugly, repetitive methods that check each criterion for
    nil/empty and then append to arrays of join tables, conditions and
    condition variables.

  2. Unsettling mix of model and controller responsibility when
    constructing Paginators

  3. Reduced form helper support for form elements that aren’t linked to a
    model class

  4. A bit too much ugly code necessary to get pagination links to send
    along the original search params

  5. No built in support for sortable table headers (okay, so this one is
    easy to implement… but still… it would be so easy to add the magic)

My current plan of attack is to add a few helper methods and extend
ActiveRecord and Pagination to support these search features in a more
elegant way.

For example, imagine you could do something like:

@bobs = Person.search(:min_birth_date => ‘2002-05-01’,
:max_birth_date => ‘2003-05-01’,
:first_name_like => ‘obert’,
:last_name_starts_with => ‘Mac’,
:gender => ‘Male’)

and have it recognize the “min_”, “max_” and “_like” modifiers and
automatically build a query with the appropriate conditions and
condition variables.

Ideally, this search method would have a way of automatically handling
joins for cross-table criteria, and also of doing a substring “keyword”
search across several named fields. All of this would be made to work
with paginate as well.

On the view side, a simple helper could write out column header links
that would rerun the search with the correct order_bys. A switch on the
existing pagination_links helper would tell it to include all of the
criteria params from the original search. A basic search method/page
could be generated as part of scaffold (for those who use it as a
reference).

Has anyone already done something similar? Or solved this problem in a
more elegant way? If so, I’d love to hear about it!

If not, I may take a stab at this over the holidays and see what I come
up with. Let me know if you want to help!

Cheers,
Erin


#2

You might have a look at this for the dynamic sql stuff:

http://brainspl.at/articles/2005/12/02/build-ar-base-finds-conditions-clause-dynamically-take-one

John

Erin Mulder wrote:

nil/empty and then append to arrays of join tables, conditions and

@bobs = Person.search(:min_birth_date => ‘2002-05-01’,
joins for cross-table criteria, and also of doing a substring “keyword”
Has anyone already done something similar? Or solved this problem in a
http://lists.rubyonrails.org/mailman/listinfo/rails

John D.
Great Basin Development
P.O.Box 3503, Reno, Nevada 89505
Cell: (775) 772-7336
removed_email_address@domain.invalid
http://www.gbdev.com/


#3

Hi,

I have come across this problem before and it was the main motivation
for the ActiveForm [1] class that I created. It allowed validations to
be used for non-activerecord objects. The next step I made a
ActiveSearch class that defined a bunch of macros similar to

search_field :type_id, :foreign_key
search_field :created_before, :date
search_field :created_from, :period
search_field :include_summary, :boolean
search_field :component_id, :foreign_key, :join => ‘components’
search_field :version_id, :foreign_key, :join => ‘versions’, :join_key
=> ‘affects_version_id’

This class also had a gen_sql that returned two values conditions and
joins that could be passed onto the paginator or directly into find
etc. The class also had methods to generate the params from a
particular search so I could link to it from sorted_link_tos or
paginator links. Of course it added benefit that it did not produce
param if the value was equivelent to the default. The only thing I
have yet to complete is correctly generating the order_bys for join
tables but I only need that in one place so I have not bothered.

[1]
http://www.realityforge.org/articles/2005/12/02/validations-for-non-activerecord-model-objects


Cheers,

Peter D.
Blog: http://www.RealityForge.org