ilike drizzle postgress
how can i disregard the order of the searchterm? im already replacing whitespaces with % with a regex but the order seems to somewhat matter for ilike? i am wondering if there is any way to have the order of each word disregarded in the queried column, it just looks if its present or not
20 Replies
Hello, @HotBBQSauce! It seems you want to pass a string, for example, "red shoes," and retrieve all rows where the column contains variations like "shoes red," "red shoes," "my lovely shoes that are red," etc. If that's the case, you might try the following approach:
Also, you might consider using full-text search capabilities.
I think i might be missing something, can you eloborate a little more, i dont need the order of the words to be respected, currently it is so i wouldnt be able to find my lovely shoes that are red when using red lovely shoes
@HotBBQSauce Using the query mentioned above, if you pass the string 'red lovely shoes' and in the database there is a row where a column has the value 'my lovely shoes that are red', this row will be returned. If this is not the intended result, could you please provide more details about your query and the expected result?
This is the intended result indeed but it does not work
For example i have Apple Iphone 15 pro max 256gb in this exact order
Im even replacing the whitespaces in the string with % so the order of the words should not matter but this does not seem the case
Iphone max pro 15
Wouldnt return the iphone 15 pro max for example
Im always adding % between a series of numbers and letters if the consequent character differs basically from the previous ones
What do you mean by full text search capibilities?
PostgreSQL Documentation
12.1. Introduction
12.1. Introduction # 12.1.1. What Is a Document? 12.1.2. Basic Text Matching 12.1.3. Configurations Full Text Searching (or just text search) …
Did you try to use the code snippet above? Because I tested it just now with your strings, and it worked out. I might be missing something.
It does work thank you so much
can you explain me what the difference is between the code you provided and what i had before so i can understand this for the next time when implementing myself
@solo ?
@HotBBQSauce sorry for late response
In the first approach, you used a regular expression to replace spaces and other characters with the % symbol in the search query, creating a single
ilike
condition with this modified string. This means your search will look for entries that exactly match the modified search string, where % acts as a wildcard for any number of characters. The order of words in the search query matters because you are looking for an exact match of the modified string in the target column.
In the second approach, you split the original search string into words and created a separate ilike
condition for each word, using % as a wildcard before and after each word. Then, you combined all these conditions using the AND
logical operation. This means your search will look for records where each word from the search string is present in the target column, regardless of their order.Have read your explanation multiple times, i understand it now!!! Thanks for the help. I am wondering if this is sql injection safe handeld by drizzle or am i supposed to do that?
Hi, @HotBBQSauce! Super! Honestly, it's a good question about sql injection. Not sure about it
Coming back to this, i have noticed that for example 256gb doesnt get picked up in the database where its stored as 256 GB. Should i just keep my regex code and map over the result which the delimiter as % instead of whitespace? And then append a trailing and leading %?
String.split(“%”)?
@HotBBQSauce ye, if you search by 256gb you won't get 256 GB. You can try with your solution, but I think this should be done with full-text search, because your logic is quite complex
Im not that familiar with the term full text search? Is this a built in sql feature or something?
Yes, this is postgresql off docs
https://www.postgresql.org/docs/current/textsearch.html
PostgreSQL Documentation
Chapter 12. Full Text Search
Chapter 12. Full Text Search Table of Contents 12.1. Introduction 12.1.1. What Is a Document? 12.1.2. Basic Text Matching 12.1.3. Configurations 12.2. …
Can i execute this functionality with drizzle sql feature or is this really something not supported by drizzle?
You can do it with
sql
operator
https://orm.drizzle.team/docs/sql#sql-in-where Example: Advanced fulltext search where statementDrizzle ORM - Magic sql`` operator
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
I see:
where(sql
to_tsvector('simple', ${usersTable.name}) @@ to_tsquery('simple', ${searchParam})
)
So search param can take the regular string. do i still need to replaces whitespaces by % to have the order of the words in the column ignored when searching?
Or does to_tsquery alreadyhandle that?Unfortunately, I am not very familiar with it, would be better to test and find some info in docs
Oke cool✌🏼 i will give it a go later ill let u know the end solution!
Maybe using elastic search or something will come in handy isnt what we are doing the intended use case?
would be good too!