Search many fields with will_paginate / searchlogic

Question

How do you effectively search among many fields in a model?

# user.rb model
def self.search(search, page)  
  paginate :per_page => 20, :page => page,
  :conditions => 
    ['name like ? OR notes like ? OR code like ? OR city like ? OR state like ?,
    "%#{search}%","%#{search}%","%#{search}%","%#{search}%","%#{search}%"
    ], :order => 'name'

This code is horrible for any more than a few fields, and it doesn't return a result if, for instance word #1 comes from :name and word #2 comes from :code. Is there a more elegant way?

The reason the search isn't finding any matches with multiple words is because it is looking for the entire string "word1 word2" in all your columns but is only finding partial matches, so make sure to separate the search terms first. — Rich, Feb 22 '11 at 23:39

score 2 · Accepted Answer · answered Nov 14 '10 at 08:11

2

I think that do work

def self.search(search, page)
  fields = [:name, :notes, :code, :city, :state] 
  paginate :per_page => 20, :page => page,
  :conditions => [fields.map{|f| "#{f} like ?"}.join(' OR '),
    *fields.map{|f| "%#{search}%"}], :order => 'name'

answered Nov 14 '10 at 08:11

shingara

46,608
11
99
105

Thank you for compressing it so nicely. I tried it with two search terms, where one is from :notes and another from :code and it doesn't work (it wasn't working before either). Is there any way to add results for each search term across fields? – sscirrus Nov 14 '10 at 08:18

score 1 · Answer 2 · edited Dec 04 '12 at 07:30

1

You can use searchlogic

def self.search(search, page)  
  search_cond = resource.search(name_or_notes_or_code_or_city_or_state_like => search.to_s)
  search_cond.all
end

Hope you got the idea

edited Dec 04 '12 at 07:30

akjoshi

15,374
13
103
121

answered Dec 21 '10 at 06:20

Anjan

11
1

score 0 · Answer 3 · answered Nov 14 '10 at 14:05

0

def self.search(search, page)
  fields = %w(name notes code city state) 
  paginate :per_page => 20, :page => page,
  :conditions => [fields.map{|f| "#{f} like :phrase"}.join(' OR '), {:phrase => search}], 
  :order => 'name'

answered Nov 14 '10 at 14:05

Tadas T

2,492
20
20

Search many fields with will_paginate / searchlogic

3 Answers3