How to add related table column in sql to_tsvector?

Question

I am working on a django API in which I need to implement a search-as-you-type feature. At first I wanted to use Django SearchVector and SearchQuery but they seem to perform poorly when query terms are incomplete (which is not ideal for the "as-you-type" part) so I went for the SQL approach. I need to be able to search on first_name and last_name of a contact as well as on the email of the user related to that contact.

I used the following to create a search_vector on contact and to add an index on this column. This works great to search on first and last name.

ALTER TABLE contact_contact 
ADD COLUMN search_vector tsvector;
    
UPDATE contact_contact
SET search_vector = to_tsvector('english', coalesce(first_name, '') || ' ' || coalesce(last_name, ''));

CREATE INDEX mc_idx2 ON contact_contact USING GIN (search_vector);

I would like to add the user email to this search_vector, something like:

...
SET search_vector = to_tsvector('english', coalesce(first_name, '') || ' ' || coalesce(last_name, '') || coalesce(user.email::text, ' '));
...

I get the following error

ERROR: syntax error at or near "." LINE 2: ...|| ' ' || coalesce(last_name, '') || coalesce(user.email::te..

The "." refers to `user.email I cannot figure out the correct syntax or process to do that. Any help is greatly appreciated!

Answer 1

then you have to join between two tables:

UPDATE contact_contact cc
SET search_vector = to_tsvector('english', coalesce(first_name, '') || ' ' || coalesce(last_name, '') || ' ' || coalesce(u.email::text, '') || ' ' || coalesce(co.name::text, '')) 
FROM user u 
JOIN company co
 On co.contact_id = cc.id 
WHERE u.contact_id = cc.id