# Searching multiple fields with conditions

**URL:** <https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914>\
**Category:** Rails\
**Created:** [May 23, 2007, 8:56pm UTC](https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914 "2007-05-23T20:56:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![hwhite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/ec9cab/32.png) [@hwhite](https://www.ruby-forum.com/u/hwhite)\
**Post date:** [May 23, 2007, 8:56pm UTC](https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914/1 "2007-05-23T20:56:23Z")

</div>

I am very new at RoR but am trying to tweak the code for the AJAX table  
from [How to paginate, sort and search a table with Ajax and Rails · dev.nozav.org](http://dev.nozav.org/rails_ajax_table.html).

I have it all working the way I want except for one line of code:

conditions = [“firstname LIKE ?” , “%#{@params[:query]}%”] unless  
@params[:query].nil?

I need this to look at multiple fields not just firstname. Any idea how  
I do this?

---

<div class="post-metadata">

**Author:** ![hwhite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/ec9cab/32.png) [@hwhite](https://www.ruby-forum.com/u/hwhite)\
**Post date:** [May 23, 2007, 9:05pm UTC](https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914/2 "2007-05-23T21:05:00Z")

</div>

On May 23, 2007, at 2:56 PM, Heather White wrote:

> conditions = [“firstname LIKE ?” , “%#{@params[:query]}%”] unless  
> @params[:query].nil?
> 
> I need this to look at multiple fields not just firstname. Any  
> idea how  
> I do this?

were you just doing = comparisons then you could extract a hash of  
conditions from the params and pass that directly. for what you’re  
doing you might look at using the ez\_where plugin:

[http://opensvn.csie.org/ezra/rails/plugins/dev/ez\_where/](http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where/)

-faisal

---

<div class="post-metadata">

**Author:** ![hwhite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/ec9cab/32.png) [@hwhite](https://www.ruby-forum.com/u/hwhite)\
**Post date:** [May 23, 2007, 9:14pm UTC](https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914/3 "2007-05-23T21:14:01Z")

</div>

> I am very new at RoR but am trying to tweak the code for the AJAX table  
> from [How to paginate, sort and search a table with Ajax and Rails · dev.nozav.org](http://dev.nozav.org/rails_ajax_table.html).
> 
> I have it all working the way I want except for one line of code:
> 
> conditions = [“firstname LIKE ?” , “%#{@params[:query]}%”] unless  
> @params[:query].nil?
> 
> I need this to look at multiple fields not just firstname. Any idea how  
> I do this?

unless params[:query].blank?  
query = “%#{@params[:query]}%”  
conditions = [“firstname LIKE ? OR lastname LIKE ?”, query, query]  
end

You can add as many OR’s as you want as long as you keep adding more  
query’s to the end.

Your database may not like you if you do this, but that’s another matter  
🙂

-philip

---

<div class="post-metadata">

**Author:** ![hwhite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/ec9cab/32.png) [@hwhite](https://www.ruby-forum.com/u/hwhite)\
**Post date:** [May 23, 2007, 9:39pm UTC](https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914/4 "2007-05-23T21:39:04Z")

</div>

Also check out ActiveRecord::Extensions for more hash-based conditions  
suck as field\_like, field\_contains, field\_lt, field\_gt, field\_ne, etc.  
([http://www.continuousthinking.com/tags/arext](http://www.continuousthinking.com/tags/arext)).

However, hash-based conditions are added together with AND rather than  
OR, so it’s not much use for this particular prooblem.

---

<div class="post-metadata">

**Author:** ![hwhite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/ec9cab/32.png) [@hwhite](https://www.ruby-forum.com/u/hwhite)\
**Post date:** [May 23, 2007, 9:28pm UTC](https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914/5 "2007-05-23T21:28:49Z")

</div>

Philip H. wrote:

> unless params[:query].blank?  
> query = “%#{@params[:query]}%”  
> conditions = [“firstname LIKE ? OR lastname LIKE ?”, query, query]  
> end

Thanks that did exactly what I needed.

---

<div class="post-metadata">

**Author:** ![hwhite](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/ec9cab/32.png) [@hwhite](https://www.ruby-forum.com/u/hwhite)\
**Post date:** [June 8, 2007, 5:51am UTC](https://www.ruby-forum.com/t/searching-multiple-fields-with-conditions/104914/6 "2007-06-08T05:51:19Z")

</div>

I am just about to release 0.7.0 for ActiveRecord::Extensions. I am  
thinking of including OR functionality. Please email me privately if  
you have comments or suggestions on this.

I am planning to have:  
Book.find :all, :conditions=\>{ :name\_like =\> “Star  
Wars”, :or\_name\_like=\>“Star Trek” }

generate SQL like:  
name LIKE "Star Wars"OR name LIKE “Star Trek”

It’s limitation is that it will not be able to group OR’d selectors,  
but it provides basic OR capabilities. thought?

Zach  
[http://www.continuousthinking.com](http://www.continuousthinking.com)