ansaurus

Question

Rails 3 - Using LIKE to search a combined 2 columns...

Answer 1

+1 A:

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.

DGM 2010-10-16 03:40:50

This works in mysql, I don't know about other db's.

DGM 2010-10-16 03:41:17

Also, my usage requires lastname, firstname, adjust the concat as needed.

DGM 2010-10-16 03:42:19

Does the concat result in a full table scan?

Mike Bethany 2010-10-16 03:46:24

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 2010-10-16 03:51:32

@Pickle, what do you mean by full table scan?

WozPoz 2010-10-17 06:21:18

@DGM, thanks, but how does this fit into the code sample above?

WozPoz 2010-10-17 06:22:26

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

Mike Bethany 2010-10-17 07:31:29

WozPoz 2010-10-17 16:17:25

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

Mike Bethany 2010-10-18 01:02:55

@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 2010-10-18 16:06:46

@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 2010-10-18 18:42:51

@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 2010-10-19 14:46:17

@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 2010-10-19 14:54:26

@DGM Which is exactly what I said... "won't help with "first_name LIKE '%bob%'" because that's not a prefix search"

Mike Bethany 2010-10-19 18:26:29

Answer 2

A:

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

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

WozPoz 2010-10-18 18:47:22

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 2010-10-18 18:57:55

ansaurus

tags:

views:

answers:

Rails 3 - Using LIKE to search a combined 2 columns...

related questions