TypeOrm: Selecting results that contain all values in array

Question

I am having some trouble getting the data I want from a Typeorm query.

Basically, we have a search bar for the users to search our database of products (medicines). I want to select all records from the database that include every word the user entered. (These words are split into an array of values.) It might not be a perfect match for the product name, so I'm thinking I should use LIKE?

We have English, Japanese, and Chinese results in the database. I want to search the English name (nameEn), Japanese name (nameJa), and Chinese name (nameZh) for any results that contain all the user's search queries.

I'm unsure how I can programmatically loop over the search queries as well as building the query for Typeorm at the same time.

Current code:

const medicines = await getRepository(Medicine)
  .createQueryBuilder('medicine')
  .where('medicine.nameEn like :query', { query: '%bufferin%' })
  .andWhere(
    new Brackets((qb) => {
      qb.where('medicine.nameEn like :query1', {
        query1: '%luna%',
      }).orWhere('medicine.nameEn like :query2', { query2: 'EX' });
    }),
  )
  .getMany();

This works for getting English products that match... but right now I've just hard coded in the query "bufferin luna EX". How would I do this if it was ['bufferin', 'luna', 'EX'], and also wanted to check nameJa and nameZh?

Thank you very much for your time.

lemonshiba · Accepted Answer · 2020-12-16 02:55:50Z

After testing it a bit more, I managed to come up with a way to do this dynamically by saving an instance of the Select Query Builder, then adding .andWhere chains for each query. Posting the code in case it helps anyone in the future.

// get instance of Select Query Builder
let builder = await getRepository(Items)
  .createQueryBuilder('item')

// validating data
  .where('item.categoryId is not null')
  .andWhere('item.companyId is not null');
    

queries.map((query, index) => {
  const queryName = `query_${index}`;
  const escapedQuery = escapeLikeString(query);

  builder = builder   
  .andWhere(`item.nameEn like :${queryName}`, { [queryName]: `%${escapedQuery}%` })
  .orWhere(`item.nameJa like :${queryName}`, { [queryName]: `%${escapedQuery}%` })
  .orWhere(`item.nameZh like :${queryName}`, { [queryName]: `%${escapedQuery}%` })
});
    
  const items = await builder.getMany();

Collectives™ on Stack Overflow

TypeOrm: Selecting results that contain all values in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related