{"id":31,"date":"2008-12-29T21:43:07","date_gmt":"2008-12-30T02:43:07","guid":{"rendered":"http:\/\/unixmonkey.net\/?p=31"},"modified":"2008-12-29T21:43:07","modified_gmt":"2008-12-30T02:43:07","slug":"activerecord-query-building-with-multiple-and-optional-conditions","status":"publish","type":"post","link":"https:\/\/unixmonkey.net\/?p=31","title":{"rendered":"ActiveRecord Query Building with Multiple and Optional Conditions"},"content":{"rendered":"<p>For some complex searches, I find myself needing to build a query with some optional conditions that may or may not exist based on search terms.  <\/p>\n<p>I could concatenate an SQL string, but when we are dealing with user-supplied input, we need to parameterize the sql query with question marks (?) for binding parameters to to avoid SQL injection attacks.<\/p>\n<p>But I may not know how many parameters I\u2019m actually going to use in a query.<\/p>\n<p>With a form like this:<\/p>\n<pre>\nShow me my friends: \n  living in: [   ]\n  whose hobby is: () skating, () fishing, () basketball\n  over: [  ] years old\n\n  *(no required fields)\n<\/pre>\n<p>At first stab, you could try something like this:<\/p>\n<pre lang=\"ruby\">\nUser.friends.find(:all, :conditions => [\n   'town LIKE ? AND hobby = ? AND age >= ?', \n   \"%#{params[:town]}%\", params[:hobby], params[:age] \n])\n<\/pre>\n<p>This would work, but only if all fields are filled out; otherwise the SQL generated would break looking like this if someone only filled in the name part of the form:<\/p>\n<pre>\n\"SELECT * FROM users WHERE town LIKE \"%indianapolis%\" AND hobby = '' AND age >= ''\"\n<\/pre>\n<p>There are some plugins like Ezra&#8217;s <a href=\"http:\/\/github.com\/ezmobius\/ez-where\/tree\">Ez-Where<\/a> to handle query building; but how about we try using the built-in symbol key interpolation <a href=\"http:\/\/noobkit.com\/show\/ruby\/rails\/rails-stable\/activerecord\/activerecord\/base.html#toc-conditions\">as described here<\/a> in ActiveRecord to get the job done.<\/p>\n<p>This lets you use named :symbols in place of question marks for binding.<\/p>\n<p>Find lets you supply your conditions as a 2-element array with a string, and a corresponding hash like so:<\/p>\n<pre lang=\"ruby\">\nUser.friends.find(:all, :conditions => [ \n  'town LIKE :town AND hobby = :hobby AND age >= :age', \n  { :town => \"%#{params[:town]}%\" , :hobby => params[:hobby],  :age = params[:age] }\n])\n<\/pre>\n<p>Again, this will work just fine if all fields are filled out; but how do we omit conditions and hash keys?<\/p>\n<p>Our final SQL string can be built by joining conditions with AND (or OR if your app suggests it), and stuffing new key\/value pairs into our arguments hash, or with Hash.merge!<\/p>\n<p>First we\u2019ll set up our search method like so:<\/p>\n<pre lang=\"ruby\">\ndef results\n  conditions  = []\n  arguments = {}\n  \n  unless params[:town].blank?\n    conditions << 'town LIKE :town'\n    arguments[:town] = \"%#{params[:town]}%\"\n  end\n  \n  unless params[:hobby].blank?\n    conditions << 'hobby = :hobby'\n    arguments[:hobby] = params[:hobby]\n  end\n\n  unless params[:age].blank?\n    conditions << 'age >= :age'\n    arguments[:age] = params[:age]\n  end\n  \n  all_conditions = conditions.join(' AND ')\n  \n  @user_friends = User.friends.find(:all, :conditions => [all_conditions, arguments])\nend\n<\/pre>\n<p>An alternative is to use Hash.merge! like so:<br \/>\n(this will let you assign more than one key\/value pair at a time or to combine hashes)<\/p>\n<p>instead of: <\/p>\n<pre lang=\"ruby\">\narguments[:town]  = \"%#{params[:town]}%\"\narguments[:hobby] = params[:hobby]\n<\/pre>\n<pre lang=\"ruby\">\n  arguments.merge!({ \n    :town  => \"%#{params[:town]}%\",\n    :hobby => params[:hobby]\n  })\n<\/pre>\n<p>Maybe not as elegant as using a plugin, but certainly clear and flexible.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>For some complex searches, I find myself needing to build a query with some optional conditions that may or may not exist based on search terms. I could concatenate an SQL string, but when we are dealing with user-supplied input, we need to parameterize the sql query with question marks (?) for binding parameters to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4,14,15,1],"tags":[20],"_links":{"self":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/posts\/31"}],"collection":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=31"}],"version-history":[{"count":0,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=\/wp\/v2\/posts\/31\/revisions"}],"wp:attachment":[{"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=31"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=31"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unixmonkey.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}