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!
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