Untitled
-- FTS prepare ALTER TABLE address ADD COLUMN source_tsvector tsvector NOT NULL DEFAULT to_tsvector(source); DROP INDEX address.address_value_gin_idx; DROP INDEX address.address_source_gin_idx; CREATE INDEX address_source_gin_idx ON address USING gin (source_tsvector); CREATE OR REPLACE FUNCTION before_insert_address_function() RETURNS TRIGGER AS $$ BEGIN NEW.source_tsvector := to_tsvector('russian', NEW.source); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER before_insert_address BEFORE INSERT ON address FOR EACH ROW EXECUTE FUNCTION before_insert_address_function(); CREATE OR REPLACE FUNCTION before_update_address_function() RETURNS TRIGGER AS $$ BEGIN IF OLD.source IS DISTINCT FROM NEW.source THEN NEW.source_tsvector := to_tsvector('russian', NEW.source); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- FTS Поиск WITH source_query AS ( SELECT garbage_source.geo_object_id, garbage_source.name, geo_object.type_id, garbage_source.latitude AS lat, garbage_source.longitude AS lon, ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) + ts_rank(to_tsvector(garbage_source.name), plainto_tsquery('Борисовское')) AS rank FROM garbage_source LEFT JOIN geo_object ON garbage_source.geo_object_id = geo_object.id LEFT JOIN address ON garbage_source.address_id = address.id WHERE garbage_source.latitude IS NOT NULL AND garbage_source.longitude IS NOT NULL AND ( to_tsvector(garbage_source.name) @@ plainto_tsquery('Борисовское') OR address.source_tsvector @@ plainto_tsquery('Борисовское') ) ), msn_query AS ( SELECT msn.geo_object_id, msn.name, geo_object.type_id, msn.latitude AS lat, msn.longitude AS lon, ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) + ts_rank(to_tsvector(msn.name), plainto_tsquery('Борисовское')) AS rank FROM msn LEFT JOIN geo_object ON msn.geo_object_id = geo_object.id LEFT JOIN address ON msn.address_id = address.id WHERE msn.latitude IS NOT NULL AND msn.longitude IS NOT NULL AND ( to_tsvector(msn.name) @@ plainto_tsquery('Борисовское') OR address.source_tsvector @@ plainto_tsquery('Борисовское') ) ), tko_object_query AS ( SELECT tko_object.geo_object_id, tko_object.name, geo_object.type_id, tko_object.latitude AS lat, tko_object.longitude AS lon, ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) + ts_rank(to_tsvector(tko_object.name), plainto_tsquery('Борисовское')) AS rank FROM tko_object LEFT JOIN geo_object ON tko_object.geo_object_id = geo_object.id LEFT JOIN address ON tko_object.address_id = address.id WHERE tko_object.latitude IS NOT NULL AND tko_object.longitude IS NOT NULL AND ( to_tsvector(tko_object.name) @@ plainto_tsquery('Борисовское') OR address.source_tsvector @@ plainto_tsquery('Борисовское') ) ), threads_query AS ( SELECT geo_object.id AS geo_object_id, geo_object.name, geo_object.type_id, geo_object.lat, geo_object.lon, ts_rank(address.source_tsvector, plainto_tsquery('Борисовское')) AS rank FROM threads INNER JOIN geo_object ON threads.geo_object_id = geo_object.id LEFT JOIN address ON geo_object.id = address.geo_object_id WHERE geo_object.lat IS NOT NULL AND geo_object.lon IS NOT NULL AND ( to_tsvector(geo_object.name) @@ plainto_tsquery('Борисовское') OR address.source_tsvector @@ plainto_tsquery('Борисовское') ) ) SELECT geo_object_id, name, lat, lon, type_id, rank FROM source_query UNION SELECT geo_object_id, name, lat, lon, type_id, rank FROM msn_query UNION SELECT geo_object_id, name, lat, lon, type_id, rank FROM tko_object_query UNION SELECT geo_object_id, name, lat, lon, type_id, rank FROM threads_query ORDER BY rank DESC;
Leave a Comment