11

I'm following ryan's Simple Search Form tutorial here: http://railscasts.com/episodes/37-simple-search-form

I have the following line in my Users Model:

find(:all, :conditions => ['fname LIKE ?', "%#{search}%"])

But what I'd like to do is search across a combine 2 columns,: fname & lname

As users are searching my full names:

Example, James Brown fname = James lname = Brown

Is there a way to do this in Rails safely that will work across DBs like SQLite, MySQL or Postgres (heroku uses)?

Thanks!

WozPoz
  • 992
  • 2
  • 15
  • 29

2 Answers2

14

It may not be pretty, but I use this in my Person model:

scope :by_full_name lambda {|q| 
    where("first_name LIKE ? or last_name LIKE ? or concat(last_name, ', ', first_name) LIKE ?", "%#{q}%", "%#{q}%" , "%#{q}%")
}

See one of my other posts for an bit extra that will let the search query be optional.

Community
  • 1
  • 1
DGM
  • 26,629
  • 7
  • 58
  • 79
  • Also, my usage requires lastname, firstname, adjust the concat as needed. – DGM Oct 16 '10 at 03:42
  • Does the concat result in a full table scan? –  Oct 16 '10 at 03:46
  • yeah it probably does, as does your single column search. But I don't know of any way to do more intensive searches without do some complex indexing such as full text search. – DGM Oct 16 '10 at 03:51
  • @Pickle, what do you mean by full table scan? – WozPoz Oct 17 '10 at 06:21
  • @DGM, thanks, but how does this fit into the code sample above? – WozPoz Oct 17 '10 at 06:22
  • @DGM A single column LIKE search wouldn't do a full table scan if the column is indexed. LIKE's in MySQL do use the index. @WozPoz A full table scan means when you do a search it looks at every value in every record instead of looking at an index. Depending on your recordset this can be very slow especially when you have many, many people hitting the database. –  Oct 17 '10 at 07:31
  • @Pickle, thanks. I'm using Heroku which I believe use PostGres, any idea if that will use an index? So I'll need to add an index to both columns fname & lname. – WozPoz Oct 17 '10 at 16:17
  • 1
    @WozPoz You can create an index that will allow indexed prefix searches, "first_name LIKE 'bob%'", using something like: create index first_name_index on people (first_name text_pattern_ops); Note that won't help with "first_name LIKE '%bob%'" because that's not a prefix search. –  Oct 18 '10 at 01:02
  • @Pickle I see thanks, that's a lot of help - now I get it... For user name searching, I imagine that prefix searching should work ok. – WozPoz Oct 18 '10 at 16:06
  • @DGM this doesn't work on my local instance (SQLite). "WHERE (fname LIKE '%Steve%' or lname LIKE '%Steve%' or concat(fname, , lname) LIKE '%Steve%'))" – WozPoz Oct 18 '10 at 18:42
  • @WozPoz I don't know if sqlite has the same concat function, but there's an error in what you wrote.. you need something between the commas. concat(first_name, ' ', 'last_name) – DGM Oct 19 '10 at 14:46
  • @PicklePumper The index fails though, when you use wildcards on both sides of the search string. see http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html and search for '%Patrick%' on the page. In the case of using wildcards, it must scan every record, using a Boyer-Moore algorithm which runs O(n). – DGM Oct 19 '10 at 14:54
  • @DGM Which is exactly what I said... "won't help with "first_name LIKE '%bob%'" because that's not a prefix search" –  Oct 19 '10 at 18:26
0

This ended up working extremely well... Not sure about performance though. Can Indexes Help This?

:conditions => ['fname || lname LIKE ?', "%#{search}%"]

WozPoz
  • 992
  • 2
  • 15
  • 29
  • ok maybe this isn't ideal. If you add another condition like: [project = ? AND'fname || lname LIKE ?', "%#{search}%", "%#{search2}%"] The query in the log gets REAL messy. not sure why though? – WozPoz Oct 18 '10 at 18:57