Ruby Text Search
Part of my current project screamed for some basic searching. In typical Ruby development fashion, the first thing I think of is “someone else must have done this already.” Sure enough, there were several, but the one the fit the best for me was the query version found here. It looked perfect, but there were a few problems.
- The Wiki messed up a lot of characters, making it difficult to get the code.
- A few things didn’t work quite right
Time to fix the problems. First, I needed to get the code onto my disk without spending hours finding all the places where the Wiki messed things up. A quick Google search for “search apply_demorgans” located what I was looking for. Now I had something that basically worked. Unfortunately, a few items did not work as advertised.
The first problem I found was that if you added a searches_on to your model object, you could not override the searchable fields. A quick update to self.searchable_fields solved that problem. The second problem was that if you included another table in your search, the searchable fields were not automatically pulled in as advertised. In addition, searchable fields did not include the table name so any searches with an include didn’t always work (need unique column names). Finally, I updated the search routine to also return a count so you can more easily paginate the results.
That’s it for now. The remaining issue I have is that include only works if the association variable name is the same as the table name. However, if you have an association with a different name or finder_sql, it doesn’t work. I don’t have a huge need for this right now, so I haven’t fixed it yet.
Anyone out there care to chime in?
Here is my final code: search.rb
DND: Edited to include link to file instead of code in the post. It looks like even Wordpress was playing some games with the file. If you download the file, remove the .txt extension.
August 10th, 2007 at 3:52 pm
Dave, I just tried this and I’m getting a few errors. I was getting complaints about the regular expressions in the process_chunk method, it looks like these characters need to be escaped:
/^\+/
/^\(/
/\)$/
Now that I’ve done that, I’m getting this error: “bad value for range”
The application trace is:
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.2/lib/active_support/whiny_nil.rb:35:in `make_chunks’
#{RAILS_ROOT}/lib/search.rb:205:in `lex’
#{RAILS_ROOT}/lib/search.rb:283:in `parse’
#{RAILS_ROOT}/lib/search.rb:361:in `build_text_condition’
#{RAILS_ROOT}/lib/search.rb:94:in `search’
#{RAILS_ROOT}/app/controllers/recipes_controller.rb:83:in `search’
I’ve got this application hosted, but I haven’t updated it with this search library yet. If it would help you debug this (and you have time). This is far beyond my abilities.
August 13th, 2007 at 11:18 am
Take a look at the orginal post again. I updated the post so search.rb is downloaded instead of in the post. I didn’t notice that Wordpress also changed some of the text when I posted it in. Let me know if this works for you now.
I’m using Ruby 1.8.6 and Rails 1.2.3.
October 11th, 2007 at 12:19 pm
Dave
Thanks for the ruby text search plugin. I find it very useful. However, I have problems with using the :only options, together with the pagination snippet you proposed. Have you tested that use-case ?
I have a search field in a table and a drop down box to restrict the search to one ore more fields. However, when I try to limit the search I got the error
“…MySQL server version for the right syntax to use near ‘))’ at line 1: SELECT count(*) AS count_all FROM people WHERE (())”
It seems located to the code row — results = [count(diff), find(:all, search_options)]
It works ok without the :only option. I have defined search_on :all in the table. I am using windows and Ruby 1.8.6 and Rails 1.2.3.
Any suggestions ?
October 11th, 2007 at 12:50 pm
Thanks for the catch. It looks like you caught a side effect of me using table names to support includes where the column names are not unique. When specifying :only or :except, you need to include the table name. For example, if you have a people table and you are searching, instead of:
Person.search(’jones’, :only => ['last_name'])
use
Person.search(’jones’, :only => ['people.last_name'])
The reason is that Person.searchable_fields returns all field names with the table name prepended (table.attribute). When you specify :only, you need to also be specific with your attribute names by including the table name.
Hope this helps!
October 11th, 2007 at 3:31 pm
Thanks - That helped !
You really answered directly
The proposed change made it work, so no are all my tables searchable.
How about indexing the fields in MySQL ? Would that imporve performance in any important way ?
October 11th, 2007 at 3:55 pm
Indexes are usually a good thing, but you need to weigh the extra cost of writes. If your database does a lot or writes, you will need to balance the number of indexes with write performance. However, if there are a few string columns that you do a lot of searching on, then it’s worth adding an index for those.
In the end, try a few combinations and tune based on how your application accesses the databases. However, I wouldn’t spend too much time tuning without hard data and enough traffic to make it worth it. It’s too easy to spend hours tuning for something that an end user will never realize.
October 17th, 2007 at 10:54 am
Do I need to “require”, “include” or otherwise let my application know this file exists? I’m still getting “undefined method `searches_on’ for Medium:Class”
Thanks for your time.
October 17th, 2007 at 11:05 am
You do need a require “search” for your classes. For example:
require “search”
class Contact < ActiveRecord::Base
searches_on :first_name, :last_name, :description
end
November 12th, 2007 at 1:11 pm
DAve, first of all, thanks for this amazing add to the rails search, it works great!! one question i need to ask you, is if you can give some examples of how to use the “order” option, for example to order by the field “title” in “desc” order.
Thanks in advance!
November 12th, 2007 at 1:49 pm
You can use order just like in a finder method.
Person.search(’director’, :order => ‘people.title desc’)
I usually put the table name before the column to avoid any unique column name errors, but in this case it is not necessary.
November 15th, 2007 at 12:52 am
thanks dave!! i made it this way: (Publication is the class, and @keywords is an array of strings, @c_order is either ‘title’ or ‘author’, and @c_sort is ‘asc’ or ‘desc’)
@search_result = Publication.search @keywords, :include => [:author], :order => “#{@c_order} #{@c_sort}”
November 15th, 2007 at 8:45 am
Glad I could help.
December 28th, 2007 at 9:07 pm
Thanks for the code. Very helpful as it seems to be doing the trick for me. One question: I need to add additional conditions to the search. For instance, I need to make sure a user has permissions to see the record. With it, I assumed I would type something like this:
Tmp.search(params[:s], :conditions => ["user_id = ?",session[:user_id]], …
where session[:user_id] = 3 and it would tack the appropriate conditions on the end. Instead, I get a SQL error:
Mysql::Error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?3)’ at line 1: SELECT count(*) AS count_all FROM tmps WHERE ((tmps.title like ‘%time%’ or tmps.description like ‘%time%’) AND user_id = ?3)
I don’t know enough of what I am looking at to know how to fix it but can see that the ? isn’t being replaced as appropriate. Any suggestions?
December 29th, 2007 at 10:03 am
Answer my own question after thinking about it over night (it makes for some very early mornings): looks like the ? option doesn’t work but the placeholder method does. This worked just fine:
… :conditions => ["user_id = :user",{:user => session[:user_id]}] …
December 29th, 2007 at 10:07 am
I lied. It worked fine if not logged in (session[:id] = nil, which the search will turn up certain things) but when logged in I get a similar error as my post from last night. Would still appreciate your input on this.
December 29th, 2007 at 10:36 am
You are right. The ? substitution isn’t enabled. The code simply joins your conditions to the generated conditions with an AND. Try this:
Tmp.search(params[:s], :conditions => “user_id = #{session[:user_id]}”)
This works for me.
Note, I also always try to use the table names (tmps.user_id). It helps the db, and it saves you from issues later if you add an include to your search.
December 29th, 2007 at 4:29 pm
Thanks, Dave. I am new to all this so forgive my ignorance. Is there a risk with this regarding SQL injection?
December 29th, 2007 at 6:00 pm
There should not be. Nothing is ever sent directly to SQL. The search strings are all parsed out first. However, when you build your conditions strings, you should be careful about injections. I haven’t used the extra conditions much, so I haven’t worried about it yet. For things like you are doing, I’m doing something like:
@current_user.tmps.search(params[:s])
This ensures that I am restricted to only the current user’s tmps. Note, @current_user is always setup by a before filter.
December 29th, 2007 at 7:54 pm
Thanks. Using @current_user doesn’t quite work for what I am doing but normally I would agree.
March 5th, 2008 at 4:48 pm
Dear Dave,
Thank you vey much for the code. It worked well.
I just want to give a quick warning to about case sensitivity. If you are using Oracle you need to uncomment the following code:
# DND: no need for this since mysql is already case insensitive.
unless options[:case] == :sensitive
text.downcase!
fields.map! { |f| “LOWER(#{f})” }
end
Thanks again for your help.
March 5th, 2008 at 9:00 pm
My pleasure. Thanks for the comment. I don’t know why I bothered to comment section out since I never would have used that option anyway. I’ve always used MySQL or MS SQL Server, and both of those default to case insensitive searches. Therefore, I pulled it out for my purposes.
May 15th, 2008 at 1:39 pm
Hi Dave, I found the code in the rails wiki and gave it a try… It was giving me a little error with the :includes, but I’ve tested your version and works perfect for me!
I felt I should leave you a message: Thank you so much
May 15th, 2008 at 1:51 pm
My pleasure. Glad I could help.