Postgresql select by array of substrings

3axap
December 26, 2024
116 views
0 votes
2 Answers

I have the following db schema

Product

Id	Name	ManufactureId
Guid (PK)	string	(FK)
–	ECOSYS M2640idw	3
–	Infoprint 1140	2

Manufacture

Id	Name
(PK)	string
1	Hitachi
2	IBM
3	Kyocera Mita

Both names (product and manufacturer) can consist of several words;

I have a search string (provided by user from mobile app) compiled in a random order ( product name manufacture name or manufacture name product name) names could be partial, I need to select db entries that will match with user input, there is no delimiters in search strings between manufacture and product parts.

search input: Kyoce ecos should provide in results an entry ECOSYS M2640idw
search input: kyocera mi ecosys should provide in results an entry ECOSYS M2640idw
search input: ibm should provide results Infoprint 1140

Could you please help to find a better way to construct SQL query to approach this on Postgresql

Thanks, for any help

Tags: postgresql

Answers

Solution:

CREATE MATERIALIZED VIEW product_search AS
SELECT
    p.id AS product_id,
    p.name AS product_name,
    m.name AS manufacture_name,
    to_tsvector('english', p.name || ' ' || m.name) AS search
FROM
    product p
LEFT JOIN
    manufacture m ON p.manufactureid = m.id;

-- Index the tsvector column
CREATE INDEX product_search_idx ON product_search USING gin(search);

https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html

Query:

SELECT product_id, product_name, vendor_name
FROM product_search
WHERE search @@ to_tsquery('english', :bind_query_here);

Fulltext:

:bind_query_here = "+word1 +word2" --must contain
:bind_query_here = "word1 word2"  --can contain
:bind_query_here = "-word1 -word2" --cant contain
:bind_query_here = "word1 | word2" --1or2 contain

https://neon.tech/postgresql/postgresql-indexes/postgresql-full-text-search

- ValNik
- December 26, 2024 at 3:03 pm
- 0 votes
0
JOIN Product and Manufacturer and string_to table(search input).
Then use LIKE or ILIKE for search words and word parts.

You need to decide whether you will
- search by whole words or by part of a word
- case sensitive or ignore case
For search whole words solution example provided by @Mertuarez.
I suggest example for search parts of words.
```
select *
from Product p
left join Manufacture m on p.ManufactureId=m.Id
inner join string_to_table('kyo',' ') ws 
   on   p.name ilike concat('%',ws,'%') 
     or m.name ilike concat('%',ws,'%') 
```
id name manufactureid id name ws

100 ECOSYS M2640idw 3 3 Kyocera Mita kyo

For search input ‘kyo print’ output is

id name manufactureid id name ws

100 ECOSYS M2640idw 3 3 Kyocera Mita kyo

200 Infoprint 1140 2 2 IBM print

fiddle
Login or Signup to reply.