CODEWITHSUNDEEP
sqlruby-on-railssearch-engine
zabumba
zabumba

Reputation: 12402

One field search to match several columns in the database

Env: Ruby on Rails, Postgres

I am getting challenged to make a search that would match the name of a service and and its location ...

ex: "Pizza in Paris"

I believe this would require a syntax analyser before forming the SQL query ...

I am just wondering if there is a technology out there that I am not aware of, ... yes somethings like a search engine for my database.

Upvotes: 0

Views: 90

Answers (1)

DaveB
DaveB

Reputation: 3083

With PostgreSQL You can create a tsv_vector column in your table that references multiple columns, add an update trigger for that table to keep it up to date and using full text searching to construct your queries

create table food(
    table_id serial primary key,
    food_type text not null,
    location text not null,
    food_tsv tsvector
);

Create table trigger

CREATE TRIGGER tsvector_food_update BEFORE INSERT OR UPDATE ON food
FOR EACH ROW EXECUTE PROCEDURE 
tsvector_update_trigger(food_tsv, 'pg_catalog.english', food, location);
CREATE INDEX food_index_tsv ON food USING gin(food_tsv);

Sample SQL query will give you results ordered by relevance

SELECT * FROM (
    SELECT DISTINCT ON (f.table_id) f.*, ts_rank_cd(f.food_tsv, query) AS rank FROM to_tsquery(searchTerm) AS query, food as f
    WHERE (f.food_tsv @@ query)
    ORDER BY f.table_id DESC LIMIT :limit OFFSET :offset
) AS sub ORDER BY rank DESC

Upvotes: 1

Related Questions