I''ve run into an issue with the complexity of generating the :conditions array using multiple, *optional* search parameters. I have two search terms: user_id and category. Because they are optional, if not specified the controller should not filter on that column name. I essentially have to build the condition array for each possible search combination. It works but it is unnecessarily complex, especially when I want to add a 3rd search parameter. dif list # filter_by_* is set by before_filter conditions = [] if @filter_by_user && @filter_by_categories conditions << "user_id=? and category in (?)" conditions << @filter_by_user conditions << @filter_by_categories elsif @filter_by_user conditions << "user_id=?" conditions << @filter_by_account.id elsif @filter_by_categories conditions << "zipcode in (?)" conditions << @filter_by_categories else conditions << ''1'' # TODO: This is also stupid end @xyz_pages, @xyzs = paginate :xyz, :per_page => 10, :conditions => conditions, :order_by => "created_at desc" end I attempted to go the path of xyz.find_by_* but it looks like paginate wants to take care of the query on its own. I''m hoping someone has found a nice way to do this, or that :conditions is smarter than I think it is. Any ideas? Kevin E. Hunt
I handle this by building up an array of "where" and an array of "bind": if params[:x] # one for each param @where << "x = ?" @bind << params[:x] end @where_string = @where.join(" and ") then in the find() :conditions => [@where_string, @bind].flatten this is an untested excerpt from code that *does* work Kevin E. Hunt wrote:> I''ve run into an issue with the complexity of generating the > :conditions array using multiple, *optional* search parameters. I > have two search terms: user_id and category. Because they are > optional, if not specified the controller should not filter on that > column name. > > I essentially have to build the condition array for each possible > search combination. It works but it is unnecessarily complex, > especially when I want to add a 3rd search parameter. > > dif list # filter_by_* is set by before_filter > conditions = [] > if @filter_by_user && @filter_by_categories > conditions << "user_id=? and category in (?)" > conditions << @filter_by_user > conditions << @filter_by_categories > elsif @filter_by_user > conditions << "user_id=?" > conditions << @filter_by_account.id elsif > @filter_by_categories > conditions << "zipcode in (?)" > conditions << @filter_by_categories > else > conditions << ''1'' # TODO: This is also stupid > end > > @xyz_pages, @xyzs = paginate :xyz, > :per_page => 10, > :conditions => conditions, > :order_by => "created_at desc" > end > > I attempted to go the path of xyz.find_by_* but it looks like paginate > wants to take care of the query on its own. > > I''m hoping someone has found a nice way to do this, or that > :conditions is smarter than I think it is. Any ideas? > > Kevin E. Hunt > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
I do the same thing. You can generate some complicated queries this way: cond = [] params = [] if param[x] cond << "sql" params << some_value inner_cond = [] inner_cond << "(" if bar inner_cond << "sql" else inner_cond << "false" end inner_cond << ")" cond << inner_cond.join(" OR ") end find :all, :conditions => [cond.join(" AND "), *params] Just be sure to keep cond and params in sync otherwise you''ll get weird SQL errors. Steve Downey wrote:> I handle this by building up an array of "where" and an array of "bind": > > if params[:x] # one for each param > @where << "x = ?" > @bind << params[:x] > end > > @where_string = @where.join(" and ") > > then in the find() > > :conditions => [@where_string, @bind].flatten > > this is an untested excerpt from code that *does* work > > Kevin E. Hunt wrote: > >> I''ve run into an issue with the complexity of generating the >> :conditions array using multiple, *optional* search parameters. I >> have two search terms: user_id and category. Because they are >> optional, if not specified the controller should not filter on that >> column name. >> >> I essentially have to build the condition array for each possible >> search combination. It works but it is unnecessarily complex, >> especially when I want to add a 3rd search parameter. >> >> dif list # filter_by_* is set by before_filter >> conditions = [] >> if @filter_by_user && @filter_by_categories >> conditions << "user_id=? and category in (?)" >> conditions << @filter_by_user >> conditions << @filter_by_categories >> elsif @filter_by_user >> conditions << "user_id=?" >> conditions << @filter_by_account.id elsif >> @filter_by_categories >> conditions << "zipcode in (?)" >> conditions << @filter_by_categories >> else >> conditions << ''1'' # TODO: This is also stupid >> end >> >> @xyz_pages, @xyzs = paginate :xyz, >> :per_page => 10, >> :conditions => conditions, >> :order_by => "created_at desc" >> end >> >> I attempted to go the path of xyz.find_by_* but it looks like >> paginate wants to take care of the query on its own. >> >> I''m hoping someone has found a nice way to do this, or that >> :conditions is smarter than I think it is. Any ideas? >> >> Kevin E. Hunt >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >