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: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • bodytext
  • del.icio.us
  • Reddit
  • Ma.gnolia
  • MisterWong
  • Furl
  • Netscape
  • NewsVine
  • Simpy
  • Spurl
  • StumbleUpon
  • Taggly
  • YahooMyWeb

This post was written by Michele 10 months, 4 weeks ago on October 4th, 2007 late afternoon.
Tags: , , , , , , , .

Comments feed Comments (38 so far)

David Rice 10 months, 3 weeks 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 10 months, 3 weeks ago

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

dan baxter 10 months, 3 weeks 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 10 months, 3 weeks 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 10 months, 2 weeks 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 10 months, 1 week 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 10 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 9 months, 1 week 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 9 months, 1 week ago

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

Will 8 months, 3 weeks 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 8 months, 3 weeks 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 8 months, 3 weeks ago

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

Michele 8 months, 3 weeks 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 6 months, 4 weeks ago

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

Michele 6 months, 4 weeks ago

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

starfry 6 months, 3 weeks 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 6 months, 2 weeks 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 6 months, 2 weeks 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 4 months ago

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

thanks.

Michele 4 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 3 months, 2 weeks 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 2 months, 2 weeks 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 2 months, 2 weeks 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 2 months, 1 week 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 2 months, 1 week 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 2 months, 1 week 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 2 months, 1 week 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 2 months, 1 week ago

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

Does it work with the named scopes in rails 2.1?

starfry 2 months, 1 week 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 2 months, 1 week ago

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

starfry 2 months 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 2 months 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. :)

Jam 1 month, 1 week ago

Hi!

I’m having a problem with will_paginate…

I get this error:

undefined method `total_pages’

Can anyone give me a hand with this one?

Brian Law 1 month, 1 week ago

I am a newbie at rails, just looking to see if you had any suggestions as to how I would hook this search into one model (Tape) and the def for the controller and the code for the search form. Any help appreciated

Ryan L 1 month, 1 week ago

Hey there, a friend recommended this plugin and i have been trying to install. I added actsasfulltexable to my models, but when i try to rake the migration, i get this error:

undefined method `fields’ for #

also, when trying to search multi models, where do you put the multi model search?

Ryan L 1 month, 1 week ago

Hey again, still having trouble installing. Not sure what i am doing wrong.

i add actsasfulltextable :fields… to each model, generate the migration with the models, but when i rake, it always aborts.

I tried replacing the :fields with :name, :description, …. but it didn’t like that either.

Any suggestions for install?

john 3 days, 12 hours ago

This is very interesting siteb

bdawg 2 days ago

Any word on anyone getting this to work with mislavwillpaginate?

Post a comment

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