Thread: tsvector Column Indexing Across Two Tables

Hey list,

I have a forum. I'm in the process of adding a full-text search. The
forum is split into a couple tables, including forums_posts and
forums_topics. The latter contains only the title of the topic and
some other information, like an ID number. The forums_posts table
contains the body of the post, the topicid it belongs to, and some
other things.

I'd like to index the title of the topic as well as the body of the
posts in a single tsvector column. I'm investigating ways to do this.

I created a tsvector column in forums_topics. I would have to somehow
LEFT JOIN the forums_posts table to get the body of the post.
Something like this:

ALTER TABLE "forums_topics" ADD COLUMN "search_index" tsvector;
UPDATE "forums_posts" SET "search_index" = to_tsvector('english',
coalesce("forums_topics"."subject", '') || ' ' ||
coalesce("forums_posts"."body", '')) FROM "forums_topics" ON
("forums_posts"."topicid" = "forums_topics"."id");

I don't think this would be the correct JOIN in the UPDATE clause. It
would need to be a `topics LEFT JOIN posts` type join. I'm not sure
how to do this properly.

Also, how would a trigger work in this case? tsvector_update_trigger()
does not work across tables, does it? I would have to write my own
procedure in order to correctly use a trigger. I'm not sure how to
begin doing that.

Thanks.

On Sun, Sep 6, 2009 at 9:57 PM, APseudoUtopia<apseudoutopia@gmail.com> wrote:
> Hey list,
>
> I have a forum. I'm in the process of adding a full-text search. The
> forum is split into a couple tables, including forums_posts and
> forums_topics. The latter contains only the title of the topic and
> some other information, like an ID number. The forums_posts table
> contains the body of the post, the topicid it belongs to, and some
> other things.
>
> I'd like to index the title of the topic as well as the body of the
> posts in a single tsvector column. I'm investigating ways to do this.
>
> I created a tsvector column in forums_topics. I would have to somehow
> LEFT JOIN the forums_posts table to get the body of the post.
> Something like this:
>
> ALTER TABLE "forums_topics" ADD COLUMN "search_index" tsvector;
> UPDATE "forums_posts" SET "search_index" = to_tsvector('english',
> coalesce("forums_topics"."subject", '') || ' ' ||
> coalesce("forums_posts"."body", '')) FROM "forums_topics" ON
> ("forums_posts"."topicid" = "forums_topics"."id");
>
> I don't think this would be the correct JOIN in the UPDATE clause. It
> would need to be a `topics LEFT JOIN posts` type join. I'm not sure
> how to do this properly.
>
> Also, how would a trigger work in this case? tsvector_update_trigger()
> does not work across tables, does it? I would have to write my own
> procedure in order to correctly use a trigger. I'm not sure how to
> begin doing that.
>
> Thanks.
>

Sorry to post again. I was reading over the documentation and I
discovered that it is possible to concatenate two tsvector's together.
So I can concat the subject tsvector index from the forums_topics
table with the post body in forums_posts.

On Sun, 6 Sep 2009, APseudoUtopia wrote:

> Sorry to post again. I was reading over the documentation and I
> discovered that it is possible to concatenate two tsvector's together.
> So I can concat the subject tsvector index from the forums_topics
> table with the post body in forums_posts.
>

If you read further documentation you'll discover it's wise to assign
different weights for subject and body for better ranking.


     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83