Our first plugin: acts_as_fulltextable

2007.10.04
Read Comments

Update (05/10/2007): we’ve moved the plugin to Google Code.

We’ve never been really happy with what was available to perform full-text searches in Rails.
We tried a whole bunch of different plugins — most notably acts_as_ferret and acts_as_sphinx –, but none seemed to work as expected.

Ferret is great, but we couldn’t determine why it was throwing all kinds of indexing errors at us.
After a short investigation, we supposed Ferret — or more probably acts_as_ferret — couldn’t cope with the high number of writes in our system.
So we decided to move to Sphinx and while it seemed to be working fine for a while, it suddenly started behaving strangely: the daemon was running, but the plugin couldn’t connect to it, then it stopped updating the index and so on.

I’m sure we could’ve find the culprits for all of those issues, but in the end we hadn’t got the time to do it.
That’s why we decided to move to MySQL’s very own full-text search.

MySQL full-text support might be less powerful than Ferret’s, still, it should work for 80% of the times in which full-text search is needed.
We only had two issues with it:

  1. Indexing only works for MyISAM tables
  2. We didn’t want to have a separate index for all of the different tables we had to search into, since most of the times we would have to search in all of them at once

The fix was kinda easy: we would use a dedicated MyISAM table to perform searches.
The table needed to have a way to refer to the original objects, so we decided to opt for the same approach adopted by polymorphic associations: storing both id and type.
Then we had to store the data and, given we didn’t need to give different weights to different fields, we decided to store all of the data in a single field, actually merging different fields into one.

So, a few hours — and coffees — later we had a working plugin that updates the searchable table after each save is made and performs fast and reliable searches.
Of course, the plugin is available for your own searching pleasure. Just keep in mind it doesn’t allow for the sort of flexibility you might have using something like Ferret, however, it should be just perfect if all you need is to perform basic searches on a few tables.

Keep in mind we only spent a few hours on it and it definitely needs some more love — tests, I’m looking at you –, but it works, and we’re currently using it.
Should you have any issues, please submit a bug report or send an email to info at wonsys dot net.

You can install the plugin by following the steps above:

  1. Install the plugin:
    script/plugin install http://wonsys.googlecode.com/svn/plugins/acts_as_fulltextable/

  2. Add the following code to the model that should be included in searches:
    acts_as_fulltextable :fields, :to, :include, :in, :index

  3. Create the migration:
    script/generate fulltext_rows model1 model2 model3 ...
    Then execute it:
    rake db:migrate

To perform searches you can:

  1. run a search on a single model:
    Model.find_fulltext('query to run', :limit => 10, :offset => 0)
  2. run it on more models at once:
    FulltextRow.search('query to run', :only => [:only, :this, :models], :limit => 10, :offset => 0)
Share and Enjoy:
  • Digg
  • del.icio.us
  • Reddit
  • Ma.gnolia
  • MisterWong
  • Furl
  • Netscape
  • NewsVine
  • Simpy
  • Spurl
  • StumbleUpon
  • Taggly
  • YahooMyWeb

This post was written by Michele 1 year, 9 months ago on October 4th, 2007 late afternoon.
Tags: , , , , , , , .

Comments feed Comments (34 so far)

David Rice 1 year, 8 months ago

Oh nice :) All the boxes ticked for me, have had all of those pain points and was considering giving sphinx a try but it doesn’t (or didn’t) support multi model search. Will definitely drop you some feedback when I get a chance to try it out on our project.

Michele 1 year, 8 months ago

Hey David, let us know how it works for you, we’re always keen to receive feedback. :)

dan baxter 1 year, 8 months ago

Did you try Solr and actsassolr. We moved away from ferret because of the same problems. Solr is build on Lucene similar to ferret by way more stable and it has more features.

Michele 1 year, 8 months ago

Dan, I know, but it seems overkill having to use Java to do basic searches, that’s why we wrote this plugin: in a couple of hours we had native Rails+MySQL fulltext search.

It’s not as versatile as Lucene/Solr/Ferret, but it’s good enough… :)

Brian Ketelsen 1 year, 8 months ago

We had SOLR and actsassolr for a while, but found that mysql’s full text search was what we really needed and that we weren’t really using much of solr’s power at all. Don’t miss it a bit.

RainChen 1 year, 8 months ago

aha,I found that there is already a same kind plugin exists:
http://blog.antiarc.net/2007/05/01/introducing-actsasfulltext_indexed/

it’s quite simple.But it offers a very clean way to enable user to orverride the index token:
class Thread < ActiveRecord::Base
hasmany :posts
actsasfulltextindexed

def build_index_string
    self.title + posts.collect {|post| post.body}.join(" ")
end

end

Peter Marklund 1 year, 8 months ago

Hi!
Many thanks for releasing this plugin! I used it successfully on a client project and posted some thoughts here:

http://marklunds.com/articles/one/373

Cheers

Peter

Zak 1 year, 7 months ago

I can’t get past this error

rake db:migrate
(in /Users/keith/Zak/rails/undersvn/trunk)
== CreateFulltextRows: migrating ==============================================
– createtable(:fulltextrows, {:options=>”ENGINE=MyISAM”})
-> 0.0183s
– find(:all)
rake aborted!
undefined method `find’ for #

Michele 1 year, 7 months ago

@ Zak: Did you give the generator at least one valid model to perform the migration on?

Will 1 year, 6 months ago

I can’t seem to figure out something that should be simple, say you have a document model that you have decided to fulltext index the content and title fields, the table also has a is_active field,

I would have assumed that:
Document.findfulltext(’phrase’, :conditions=>’isactive = 1′) should only show active documents, but no. All matching records are returned even if inactive. Is there a different approach I need to be taking here?

Michele 1 year, 6 months ago

@ Will: actually, a few hours ago I added support for a custom parent_id field.

It should be used for cases when you want to search only records for a given object, but is sounds like you could use the is_active field as the parent_id.
Let me know if it works for you!

Will 1 year, 6 months ago

I’m not sure I understand. How do I use the parentid as a isactive field?

Michele 1 year, 6 months ago

You should add :parent_id => :is_active as the last argument of acts_as_fulltextable in your model.
Then, when doing a search, pass the option :parent_id => value.

Also make sure you recreate your index: I’d suggest you issue a script/generate fulltext_rows model1 model2 model3 ... and, before running rake db:migrate you edit the migration and add drop_table :fulltext_rows at the beginning of up so that you start from scratch and have all indexes correctly built.

starfry 1 year, 5 months ago

Nice plugin!
Can you please tell me how I would use the find results with a pagination helper?

Michele 1 year, 5 months ago

@ starfry: I’ll have to see how to make it work with something like will_paginate, it’d be very useful…

starfry 1 year, 4 months ago

Hi Michele, I agree that using it with will_paginate is the obvious way to go. Have you any thoughts on making that work ? I’d be very keen to try it out.

starfry 1 year, 4 months ago

Hello again, just a quick line to say I have got willpaginate running on the other queries in my app and it’s great- definately the way to go. If you develop a way to use this with actsas_fulltextable I’d appreciate it…

fractious 1 year, 4 months ago

Yeah, without support for pagination (i.e. an easy way to do a full count on a query) this plugin is of limited use I’m afraid.

felipe 1 year, 2 months ago

hi, i cant use with will_paginate, have you a example?

thanks.

Michele 1 year, 2 months ago

The plugin now supports will_paginate: it’s enough that you install the plugin and then pass the :page option to the finder. It’s that easy, just make sure you’ve got the latest version of the plugin. :)

Francis Irving 1 year, 1 month ago

If you want to try out Xapian, I’ve made an actsasxapian Rails plugin which is pretty good and used on a production site now.

http://permalink.gmane.org/gmane.comp.search.xapian.general/6140

starfry 1 year ago

I am probably being thick, but I can’t get find_fulltext to paginate. In my code, I have:

@items = Item.findfulltext(searchtext,
:page => params[:page] )

This does not paginate. If I replace with:

@items = Item.paginate :page => params[:page],
:conditions => search_query

my pagination works just fine. Can you please explain what I am doing wrong with find_fulltext ?

An example would be most welcome :)

Michele 1 year ago

Hi starfry,

What you’re doing is exactly what you should be doing. Just make sure you have the latest version of the plugin installed.

You might want to delete it and the reinstall it: ./script/plugin install git://github.com/wonsys/acts_as_fulltextable.git

Let me know how it goes! :)

Scott 1 year ago

Hello Michele,
I’ve just started with installing your plugin, thanks for your work and opening your plugin up for others.

I had to do a bit of work to get the plugin installed using git, so I thought I’d send it your way if it will help other folk. I am using Mepis 7 (based on Debian). Needed to install Git-core. Then, I am using Rails 2.0.2, so I had to apply a patch from here: http://dev.rubyonrails.org/changeset/9049 to command/plugin.rb, except I had to drop the --depth 1 from line 275 (--depth wasn’t a valid option for the git-clone command) base_cmd = "git #{cmd} --depth 1 #{uri} \"#{root}/vendor/plugins/#{name}\"" .
After that I was able to install the plugin. Once I’ve done some work to get it working with my Application I’ll drop another note to say how things went.
Thanks again.

Scott 1 year ago

Well the search works well, but I can’t figure out how to get it to cooperate with will_paginate. I keep getting undefined method total_pages for #
I’ve been poking around and it seems that acts_as_fulltextable doesn’t give a collection that responds to the total_pages method. I’m too new to ruby and rails, to see my way through the code, so far. I’ll keep searching for a solution, but if anyone has some insight on this it would be great. It seems those using ferret and sphynx are running into the same issue.

Michele 1 year ago

Hi Scott,

I think I understand what’s happening: the latest version of will_paginate dropped page_count in favor of total_pages. I’m going to push a new version of the plugin which will handle this in a few minutes. :)

starfry 1 year ago

Michele, there is a bug whereby the perpage method in the AcctiveRecord is not being used so it defaults to a page length of 30 rows. I’ve discussed this with Artruraz (who did the WillPaginate patch) and have a patch to fix it.

I would also like to see a “conditons” filter so that only records that match the conditons are sumbitted to the FullText index. I have been thinking how to do this - If I get anywhere I will let you know.

Picker 1 year ago

Thank you for creating this (and letting use play with it).

Does it work with the named scopes in rails 2.1?

starfry 1 year ago

Michele,

I have written a patch to actsasfulltextable that allows specification of “conditions” that can be used to determine whether a record is included in fulltextable search.

I also have the patch for willpaginage perpage.

What’s the best way to send these to you?

Thanks,
John

Michele 1 year ago

@ starfry: You can open a bug on our bug tracker and attach a diff to it. Thanks for helping! :)

starfry 1 year ago

Here’s a weird one. Hopefully it is me doing something silly, but…

If I have the word “first” or any part thereof in the fulltext_rows “value” and then search on “first” or any substring (e.g. “fi”, “fir”), nothing is found.

I have tried the SQL directly (copying from the rails log file):

mysql> SELECT fulltextrows.*, match(value) against(’fir*’ in boolean mode) AS relevancy FROM fulltext_rows WHERE (match(value) against(’fir*’ in boolean mode) AND fulltextabletype IN (’Item’)) ORDER BY relevancy DESC, value ASC LIMIT 0, 30;
Empty set (0.00 sec)

mysql> select count() from fulltext_rows where value like “%fir%”;
+———-+
| count() |
+———-+
| 3 |
+———-+
1 row in set (0.01 sec)

It only seems to be around the word “first” or a substring of “first”. If the word is “firsh” the search works fine. I wonder if it is to do with “first” being a reserved word somewhere.

Wierd. Any ideas?

(I’d raise as a bug but it may just be me being silly)

Michele 1 year ago

@ Starfry: the problem is first is a stopword, thus it’s not being indexed. You can see a complete list of stopwords on MySQL’s website. :)

John 9 months, 3 weeks ago

Ryan,

I struggled with this for an hour, myself.

Inserting this line in the model did not work:

actsasfulltextable :fields, :to, :include, :in, :index

So, like you, I started wondering what to substitute for the various parameters. But what did :to and :in mean?

I read the source code for the plugin for clues, but no references to the mysterious :to and :in.

Finally, my brain, which had probably figured it out a half-hour ago, forced me to study all the fields together, and out popped the solution:

:fields, :to, :include, :in, :index

or, more usefully: “Fields to include in index”

In other words, the correct way to insert this line into your models is to list all the fields you want to include in the index.

For example, if the model has the fields ‘name’, ‘description’ and ‘address’, do this:

actsasfulltextable :name, :description, :address

Henry 7 months, 1 week ago

Thanks for this plugin. It’s exactly what I was looking for. However, being a noob, I’m not sure how you would handle presenting data back to the user when searching multiple models. That is, if you were to let someone search your entire site (say 4 models), how do you present this back to the user in a typical (google-like, with links to each) format? Iterate through each of the models returned and present the appropriate data for each? How would you determine which model-type each result would be?

TIA for your help,
Henry

Post a comment

Comment moderation is enabled. If your comment is not visible immediately, please do not resubmit it.