I have three modules, a Business which is associated Tags and Categories though join tables business_tags and business_categories. I intent to improve search hits on the business using tsvector on the database and I have a column searchable on the business which initially was built from the business’ name and description. I want to extend this to the tags and categories such that when tags and categories are assigned to the categories, these would be integrated into the searchable column.
Here’s my migration
execute("""
CREATE OR REPLACE FUNCTION public.businesses_searchable_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ begin
update
businesses b
set
searchable = setweight(to_tsvector('pg_catalog.english', coalesce(b.name, '')), 'A') || setweight(to_tsvector('pg_catalog.english', coalesce(b.description, '')), 'D') || setweight(to_tsvector('pg_catalog.english', coalesce(( select string_agg(distinct t.name, ' ') from business_tags bt join tags t on t.id = bt.tag_id where bt.business_id = b.id ), '') ), 'C') || setweight(to_tsvector('pg_catalog.english', coalesce(( select string_agg(distinct c.name, ' ') from business_categories bc join categories c on c.id = bc.category_id where bc.business_id = b.id ), '') ), 'C')
where
b.id = coalesce(NEW.business_id, OLD.business_id);
return coalesce(new, old);
end;
$function$
;
""")
# Triggers on join tables
execute("""
CREATE OR REPLACE TRIGGER business_tags_rebuild_searchable_trigger AFTER INSERT OR UPDATE OR DELETE ON business_tags FOR EACH ROW EXECUTE FUNCTION businesses_searchable_trigger();
""")
execute("""
CREATE OR REPLACE TRIGGER business_categories_rebuild_search_trigger
AFTER INSERT OR UPDATE OR DELETE ON business_categories
FOR EACH ROW
EXECUTE PROCEDURE businesses_searchable_trigger();
""")
When I attempt to insert a business, it breaks with the following error message:
error] ** (Postgrex.Error) ERROR 42703 (undefined_column) record “new” has no field “business_id”
I will appreciate any form of guidance
