listings_search.sql

-- View for search vectors
CREATE OR REPLACE VIEW vw_listing_search AS
SELECT
       stock_number,
       to_tsvector(coalesce(description, '')) ||
       to_tsvector(coalesce(b.name, '')) ||
       to_tsvector(coalesce(t.name, '')) ||
       to_tsvector(coalesce(mm.name, '')) AS search_vector
FROM listings
    LEFT JOIN brands b ON (listings.brand->>'id')::INT = b.machine_hub_id
    LEFT JOIN machine_types t ON (listings.type->>'id')::INT = t.machine_hub_id
    LEFT JOIN machine_models mm ON (listings.model->>'id')::INT = mm.machine_hub_id;