Updated Feb 19, 2009 by pirmin.kalberer
Labels:	Phase-Implementation

ActsAsTsearchMethod

How to declare acts_as_tsearch in your class

Introduction

This covers the basics of acts_as_tsearch declaration and not the find_by_tsearch method - which has a lot to it.

Also see:

Ground Rules

acts_as_tsearch will create a new column vectors in your table if it doesn't exist and update it with the indexed values. This column name can be overridden and there can be more than one of these vector columns. We discuss this in Multi Vector Searching and Migrations
acts_as_tsearch adds a tsearch_rank column to all results and default sorts (descending) on that column.
By default acts_as_tsearch will update the vector unless told not to (see Method Definition below)

Simple

This would search the description field of the blog_entries table:

class BlogEntry < ActiveRecord::Base
   acts_as_tsearch :fields => "description"
end

results = BlogEntry.find_by_tsearch("who what where")

Multiple fields

This would search multiple fields in blog_entries table:

class BlogEntry < ActiveRecord::Base
   acts_as_tsearch :fields => ["title","description"]
end

results = BlogEntry.find_by_tsearch("who what where")

Weighted search

This is still experimental. Up to 4 weights are supported, weight's are currently ignored and hard coded to the values in the example below.

This will search for fields in the users table, placing more importance on "a", then "b" and so on. Only four weights are support and only one is required.

class User < ActiveRecord::Base
   acts_as_tsearch :fields => {
      "a" => {:columns => [:first_name, :last_name, :company_name], :weight => 1.0},
      "b" => {:columns => [:short_description], :weight => 0.4},
      "c" => {:columns => [:state_name, :county_name, :city_name], :weight => 0.2},
      "d" => {:columns => [:about_me], :weight => 0.1}
      }
end

results = User.find_by_tsearch("who what where")

Multi Table Searches

This is very experimental. I would love help on this from more experienced Rails programmers on how to clean it up

Say you wanted to search blog_entries and their comments...

BlogEntry.acts_as_tsearch :vectors => {
   :fields => {
      "a" => {:columns => ["blog_entries.title"], :weight => 1},
      "b" => {:columns => ["blog_comments.comment"], :weight => 0.5}
      },
   :tables => {
      :blog_comments => {
         :from => "blog_entries b2 
                   left outer join blog_comments on 
                   blog_comments.blog_entry_id = b2.id",
         :where => "b2.id = blog_entries.id"
      }
    }
}

results = BlogEntry.find_by_tsearch("who what where")

As you can see this is pretty hackish. It's only been lightly tested with two tables so far, still a work in progress.

Complete Method Definition

To Do: needs work Fully verbose call example

Model.acts_as_tsearch :vectors => {
   :locale => "default",
   :auto_update_index => true,
   :fields => {
      "a" => {:columns => ["blog_entries.title"], :weight => 1},
      "b" => {:columns => ["blog_entries.description"], :weight => 0.4},
      "c" => {:columns => ["blog_entries.name"], :weight => 0.2},
      "d" => {:columns => ["blog_comments.comment"], :weight => 0.1}
   },
   :tables => {
      :blog_comments => {
         :from => "blog_entries b2 left outer join 
                   blog_comments on blog_comments.blog_entry_id = b2.id",
         :where => "b2.id = blog_entries.id"
      }
   },
   :another_vector => {... same format as above ...}

Comment by smir...@yevgeny.ru, Feb 05, 2009

 Model.acts_as_tsearch :vectors => {
   :local => "default",
  :auto_update_index => true,

':local' should be ':locale'.

Comment by pirmin.kalberer, Feb 19, 2009

Thanks for the feedback. I updated the Wiki page.

Comment by badmox, Mar 31, 2009

hi to pevent errors with postgesql 8.3.x the tsearch line in the model has to be

class name_of_the_model < ActiveRecord::Base
 acts_as_tsearch :vectors => {:fields => ["a","b"], :locale => "public.name_of_the_tsearch_config"} 
end

it took me several days to find out 8( i ony hope it helps the next guy 8)

Comment by barmstrong, Apr 22, 2009

Does anyone have a working example of a HABTM relationship?

I would like to do one like the multi table search example above, except with tags instead of comments.

Thanks! Brian

Comment by barmstrong, Apr 26, 2009

Well, I don't fully understand the SQL, but I was able to get it working with a many-to-many relationship

See: http://www.pastie.org/459372

class Page < ActiveRecord?::Base

has_many :tags, :through => :taggings

end

this will index the tag names

Comment by paul.cantrell, Nov 04, 2009

As far as I can tell, the multi-table example is broken: it will only index one of the blog_comments. Investigation; suggestions welcome.

Comment by paul.cantrell, Nov 04, 2009

OK, this seems to get all the associated values when indexing a to-many relationship:

acts_as_tsearch :fields => ['title', 'body',

'(select array_agg(tags.name)::TEXT
from posts_tags left outer join tags on posts_tags.tag_id = tags.id
where posts_tags.post_id = posts.id)']

barmstrong, you want to do something like this with your code.

► Sign in to add a comment