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
>
>