Drizzle Team•11mo ago

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•11mo 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.

HotBBQSauceOP•11mo 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•11mo 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?

HotBBQSauceOP•11mo 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•11mo ago

https://www.postgresql.org/docs/current/textsearch-intro.html

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•11mo 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' }
]

HotBBQSauceOP•11mo 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•11mo 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.

HotBBQSauceOP•10mo 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•10mo ago

Hi, @HotBBQSauce! Super! Honestly, it's a good question about sql injection. Not sure about it

HotBBQSauceOP•10mo 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•10mo 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

HotBBQSauceOP•10mo ago

Im not that familiar with the term full text search? Is this a built in sql feature or something?

Mykhailo•10mo ago

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. …

HotBBQSauceOP•10mo ago

Can i execute this functionality with drizzle sql feature or is this really something not supported by drizzle?

Mykhailo•10mo 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.

HotBBQSauceOP•10mo 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•10mo ago

Unfortunately, I am not very familiar with it, would be better to test and find some info in docs

HotBBQSauceOP•10mo 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•10mo ago

would be good too!

Gaming

Programming

ilike drizzle postgress