SQL to search many to many relationship with acts_as_tsearch

Question

I'm using the acts_as_tsearch plugin for rails to do full text searching with Postgresql.

They give an example of multi table search here: http://code.google.com/p/acts-as-tsearch/wiki/ActsAsTsearchMethod

But that only does a one-to-many relationship. I'm trying to get it to also search a many-to-many relationship (has_and_belongs_to_many or habtm) for tags on a page. But my SQL isn't advanced enough.

Here is what I've got so far:

  acts_as_tsearch :vectors => {
    :fields => {
      "a" => {:columns => ["pages.name"], :weight => 1.0},
      "b" => {:columns => ["pages.description"], :weight => 0.2},
      "c" => {:columns => ["tags.name"], :weight => 0.2}
    },
    :tables => {
      :tags => {
        :from => "tags INNER JOIN taggings ON tags.id = taggings.tag_id",
        :where => "((taggings.taggable_type = 'Page') AND (taggings.taggable_id = ???.id) AND ((taggings.context = 'tags')))"
      }
    }
  }

I'm not sure how to reference the page id (where I put the ???).

Thanks for your help!

Answer 1

Well, the SQL behind this would be:

select
    p.name,
    p.description,
    t.name
from
    pages p
    inner join taggings ts on
        p.page_id = ts.taggable_id
        and ts.taggable_type = 'Page'
        and ts.context = 'tags'
    inner join tags t on
        ts.tag_id = t.tag_id

So, your Ruby would look like:

acts_as_tsearch :vectors => {
  :fields => {
    "a" => {:columns => ["p.name"], :weight => 1.0},
    "b" => {:columns => ["p.description"], :weight => 0.2},
    "c" => {:columns => ["t.name"], :weight => 0.2}
  },
  :tables => {
    :tags => {
      :from => "pages p
        inner join taggings ts on
            p.page_id = ts.taggable_id
            and ts.taggable_type = 'Page'
            and ts.context = 'tags'
        inner join tags t on
            ts.tag_id = t.tag_id"
    }
  }
}

This is the standard way to get through a many-to-many table--just start at one table, join the mapping, and then join the other table. Voila! Many-to-many results!