CODEWITHSUNDEEP
javascriptmysqlprisma
Wodorek
Wodorek

Reputation: 33

Replacing queries in a loop in prisma

I have an API endpoint that needs to fetch data for 30 items, and it needs to check values in two different fields to return the correct one. I am using Prisma ORM.

In a request I get an array of objects looking like this:

{
 '6151': 12
},

Where the key is the part_num, and value is the color_id. I separate keys and values into arrays, and I am looking for each element one by one, in a loop:

  const elements = [];
  for (let i = 0; i < partNums.length; i++) {
    const element = await prisma.elements.findFirst({
      where: { AND: { part_num: partNums[i], color_id: colors[i] } },
    });
    elements.push(element);
  }

It seems to work fine, but I read that making multiple queries to the db is a bad practice, so my question is:

Is there a way to get the same result in one query?

My only idea is to get all elements by just one of those fields, and than filter out the ones that I don't need. That, hovewer, would require fetching way more elements from db in the first place, and the filtering also takes some time.

Upvotes: 0

Views: 4140

Answers (1)

Tasin Ishmam
Tasin Ishmam

Reputation: 7198

You could use a single findMany query. You would need to generate the where condition in a loop. It will be an array of OR conditions, each of which specifies one single Element record.

Here's the code with some comments to explain what it does

    let findManyOrCondition = [];  // each entry will contain a single part_nums, color_id condition pair.

    for (let i = 0; i < partNums.length; i++) {
        // AND condition between part_num and color_id is implicit
        findManyOrCondition.push({ 
            part_num: partNums[i], 
            color_id: colors[i],
        });
    }
    
    // fetch all of the elements with one query. 
    const elements = await prisma.elements.findMany({
        where: {
            OR: findManyOrCondition,
        },
    });

Upvotes: 2

Related Questions