Thread: joins with text search

hi-

supposing i have the following table structure:

vendors
- id int
- name varchar

products
- product_list_id int
- vendor_id int references vendors
- part_number varchar
- description varchar
- price
- textsearch

how can i execute some type of join and get the name of the vendor to be
included in the textsearch column of the products table?

i am currently populating the textsearch column with the following
command:

UPDATE
products
SET
textsearch=setweight(to_tsvector('english', description), 'A') ||
setweight(to_tsvector('english', part_number, 'B')
WHERE
product_list_id=3


Is there a way I can join products.vendor_id with vendors.id and get the
vendor.name in the textsearch column as well?

Thanks,
Clark

On Tue, Sep 07, 2010 at 10:42:53PM -0400, pg@slatech.com wrote:
> i am currently populating the textsearch column with the following
> command:
>
> UPDATE
> products
> SET
> textsearch=setweight(to_tsvector('english', description), 'A') ||
> setweight(to_tsvector('english', part_number, 'B')
> WHERE
> product_list_id=3
>
>
> Is there a way I can join products.vendor_id with vendors.id and get the
> vendor.name in the textsearch column as well?

Yup, you can either do a join inside the UPDATE, or do a subquery.  Here
it's probably easiest to do a correlated subquery:

  UPDATE products p SET textsearch=nullif('',
    coalesce(setweight(to_tsvector('english', p.description), 'A'),'') ||
    coalesce(setweight(to_tsvector('english', p.part_number), 'B'),'') ||
    coalesce(setweight(to_tsvector('english', (
      SELECT v.name FROM vendors v WHERE v.id = p.vendor_id)), 'A'),''))
  WHERE p.product_list_id=3;

I've put the calls to coalesce in so that if you happen to have any
fields missing you'll still get a useful text search column.

I'd also be tempted to remove the "to_tsvector" call from the
part_number, as I doubt it really is an english bit of text.  You can
probably just use it as a tsvector literal, probably quoting it first,
maybe something like:

  coalesce(setweight(quote_literal(p.part_number)::tsvector,'B'),'')

--
  Sam  http://samason.me.uk/