CODEWITHSUNDEEP
postgresql
Razvan Bretoiu
Razvan Bretoiu

Reputation: 533

Reverse Search in Postgresql

I want to know if the Postgresql can handle the following usecase ?

We have an app that crawls adverts from different platforms. The users can add searches, with the keyword and other filters attributes (price, location, etc). When we receive a new advert, we want to find all the searches that match to the advert details.

I need to check which one of these queries (searches) match with an Advert object each time when an Advert is received.

This is basically a reverse search.

UPDATE

Example:

I have this searches table:

+----+-------------------+------------+----------+ | Id | keyword | startPrice | endPrice | +----+-------------------+------------+----------+ | 1 | iphone 8 plus | 100 | 300 | +----+-------------------+------------+----------+ | 2 | Ipohne 7 | 50 | 500 | +----+-------------------+------------+----------+ | 3 | iphone 8 | 200 | 300 | +----+-------------------+------------+----------+ | 4 | headphones iphone | 10 | 300 | +----+-------------------+------------+----------+

Now I have and advert object with the following structure: { title: Sell Iphone 8 plus 32GB, white, price: 250 }

I want to make a query on searches table, to find all the searches that map to this advert. In my example I expect to return only the searches 1 and 3, because the 250 price is between startPrice and endPrice and their keyword is in advert.title: Sell Iphone 8 plus 32GB, white

Even if the advert.title contains the iphone word, I don't want to receive the searches with ID 2 and 4, because their keywords doesn't completely match (advert.title doesn't contain 7 and headphones)

Elasticsearch has Percolate Query which gives you the ability to store a query and run that query for every element in your collection.

Upvotes: 2

Views: 1507

Answers (1)

Michael Powers
Michael Powers

Reputation: 2050

If you want to match all keywords in a particular string you can use the PostgreSQL full text search feature like this:

SELECT id, keyword, startPrice, endPrice
FROM searches
WHERE
    startPrice <= 250
    AND endPrice >= 250
    AND to_tsvector('Sell Iphone 8 plus 32GB, white') @@ to_tsquery(array_to_string(string_to_array(keyword, ' '), ' & '));

Which results in the following:

 id |    keyword    | startprice | endprice 
----+---------------+------------+----------
  1 | iphone 8 plus |        100 |      300
  3 | iphone 8      |        200 |      300

This query will match any string that contains all keywords. Be careful to make sure keywords can't contain any characters containing matching symbols (e.g. !, &, |, etc).

Upvotes: 5

Related Questions