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
const result = await db.query.productsInformation.findMany({
where: ilike(productsInformation.name, "%" + searchTerm + "%"),
limit: 50,
});
const result = await db.query.productsInformation.findMany({
where: ilike(productsInformation.name, "%" + searchTerm + "%"),
limit: 50,
});
20 Replies
Mykhailo
Mykhailo10mo ago
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:
const string = 'red shoes';

const ilikeQuery = string.split(' ').map((w) => ilike(schema.users.name, `%${w}%`));

const response = await db
.select()
.from(schema.users)
.where(and(...ilikeQuery));
const string = 'red shoes';

const ilikeQuery = string.split(' ').map((w) => ilike(schema.users.name, `%${w}%`));

const response = await db
.select()
.from(schema.users)
.where(and(...ilikeQuery));
Also, you might consider using full-text search capabilities.
HotBBQSauce
HotBBQSauceOP10mo ago
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
Mykhailo
Mykhailo10mo ago
@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?
HotBBQSauce
HotBBQSauceOP10mo ago
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?
Mykhailo
Mykhailo10mo ago
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) …
Mykhailo
Mykhailo10mo ago
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.
const string = 'Iphone max pro 15';

const ilikeQuery = string.split(' ').map((w) => ilike(table.body, `%${w}%`));

const response = await db
.select()
.from(table)
.where(and(...ilikeQuery));

console.log(response);
const string = 'Iphone max pro 15';

const ilikeQuery = string.split(' ').map((w) => ilike(table.body, `%${w}%`));

const response = await db
.select()
.from(table)
.where(and(...ilikeQuery));

console.log(response);
[
{ id: 13, body: 'Apple Iphone 15 pro max 256gb' },
{ id: 14, body: 'iphone 15 pro max' }
]
[
{ id: 13, body: 'Apple Iphone 15 pro max 256gb' },
{ id: 14, body: 'iphone 15 pro max' }
]
HotBBQSauce
HotBBQSauceOP10mo ago
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
searchTerm = searchTerm.replace(
/(\d+)([a-zA-Z]+)|\s+/g,
function (match, p1, p2) {
return p1 ? "%" + p1 + "%" + p2 : "%";
}
);

const selectResult = await db
.select({
id: productsInformation.id,
name: productsInformation.name,
retailPrice: products.retailPrice,
inShopsPrice: products.inShopsPrice,
wholesalePrice: products.wholesalePrice,
})
.from(productsInformation)
.where(ilike(productsInformation.name, "%" + searchTerm + "%"))
.leftJoin(products, eq(products.sku, productsInformation.sku));
searchTerm = searchTerm.replace(
/(\d+)([a-zA-Z]+)|\s+/g,
function (match, p1, p2) {
return p1 ? "%" + p1 + "%" + p2 : "%";
}
);

const selectResult = await db
.select({
id: productsInformation.id,
name: productsInformation.name,
retailPrice: products.retailPrice,
inShopsPrice: products.inShopsPrice,
wholesalePrice: products.wholesalePrice,
})
.from(productsInformation)
.where(ilike(productsInformation.name, "%" + searchTerm + "%"))
.leftJoin(products, eq(products.sku, productsInformation.sku));
@solo ?
Mykhailo
Mykhailo9mo ago
@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.
HotBBQSauce
HotBBQSauceOP9mo ago
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?
Mykhailo
Mykhailo9mo ago
Hi, @HotBBQSauce! Super! Honestly, it's a good question about sql injection. Not sure about it
HotBBQSauce
HotBBQSauceOP9mo ago
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(“%”)?
Mykhailo
Mykhailo9mo ago
@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
HotBBQSauce
HotBBQSauceOP9mo ago
Im not that familiar with the term full text search? Is this a built in sql feature or something?
Mykhailo
Mykhailo9mo ago
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. …
HotBBQSauce
HotBBQSauceOP9mo ago
Can i execute this functionality with drizzle sql feature or is this really something not supported by drizzle?
Mykhailo
Mykhailo9mo ago
You can do it with sql operator https://orm.drizzle.team/docs/sql#sql-in-where Example: Advanced fulltext search where statement
Drizzle ORM - Magic sql`` operator
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
HotBBQSauce
HotBBQSauceOP9mo ago
I see: where(sqlto_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?
Mykhailo
Mykhailo9mo ago
Unfortunately, I am not very familiar with it, would be better to test and find some info in docs
HotBBQSauce
HotBBQSauceOP9mo ago
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?
Mykhailo
Mykhailo9mo ago
would be good too!
Want results from more Discord servers?
Add your server