Search form - need some direction / help

rubyonrails-talk
1

I’m trying to do one of those “build the sql where clause dynamically” searches based on what the user wishes to input. I haven’t found much material on this , except for this: http://blog.teksol.info/articles/2005/10/31/building-the-sql-where-clause-dynamically-in-rails

I put together the code from this little tutorial - but from what I can see it seems to still depend that all form elements are used. Not in my scenario.

This is the code so far and I’m not sure the direction I’m going in here is even good

    conditions = ['1=1']  # I thought I read that this could be used for a sql injection ?

conditions << 'category_id = :category_id' if params[:category_id]  # this seems fine (I think) however

conditions << 'state_id = :state_id' if params[:state_id]                 # two of the 5 elements are text fields so LIKE would probably make more sense, in the event
conditions << 'term_id = :term_id' if params[:term_id]                   # they typed goston or new yawk

conditions << 'city = :city' if params[:city]
conditions << 'title = :title' if params[:title]


@positions = Position.find(:all,
:conditions => [conditions.join(' AND '), params])       # I guess this is the part that is constraining the user to input all the conditions listed above ?

TIA Stuart

2

You should look at my ez-where plugin. It handles the nil params for you so you don't have to write unless params[:foo].nil? all the time. if the right hand side of a statement is nil it is excluded form the query.

http://brainspl.at/articles/2006/10/03/nested-joins-and-ez-where-update

-Ezra

-- Ezra Zygmuntowicz-- Lead Rails Architect -- ez@engineyard.com -- Engine Yard, Serious Rails Hosting -- Reliability, Ease of Use, Scalability -- (866) 518-YARD (9273)

3

Thank you Ezra, I 've looked at the page before . I think though at this early point in my Rails learning it might be better to first have an understanding how it’s done without any plugin.Still would appreciate any help in this area.

Stuart