skip to Main Content

I have 2 models. Database management system Postgres

model Product {
 id          Int      @id @default(autoincrement())
 name        String   @unique
 description String?
 options     Option[]

 @@map("products")
}

model Option {
 id                      Int     @id @default(autoincrement())
 title                   String?
 description             String?
 productId               Int
 product                 Product @relation(fields: [productId], references: [id]
 @@map("options")
}

I want to filter "Products" by the related table "Option" for this I use the following expression

const product = await this.prismaService.product.findMany({
        where: {
            options: {
                some: {
                    AND: [
                        {
                            title: 'square',
                            description: '20',
                        },
                        {
                            title: 'type',
                            description: 'invertor',
                        },
                    ],
                },
            },
        },
        include: { options: true },
    });

With this expression, I will get an empty array, and the β€œOR” operator will work as it should. And even if I place one object in the "AND" array, I will also get the correct filtering. Please indicate what I’m doing wrong and help me solve the problem.

2

Answers


  1. Chosen as BEST ANSWER

    This is the right solution, the authorship is not mine, the guys helped me find it.

            const product = await this.prismaService.product.findMany({
            where: {
                AND: [
                    { price: 21.99 },
                    { options: { some: { title: 'invertor', description: '25' } } },
                    { options: { some: { title: 'type', description: 'invertor' } } },
                    { options: { some: { title: 'WiFi:', description: 'yes' } } },
                ],
            },
            include: { options: true },
        });
    

  2. It seems like the issue might be related to the way you are using the AND operator within the some filter. The some filter is designed to check if at least one element in the array satisfies the conditions, and the AND operator inside it might not be working as expected.

    You can try below update version

    const product = await this.prismaService.product.findMany({
      where: {
        options: {
          every: {
            OR: [
              { title: 'square', description: '20' },
              { title: 'type', description: 'invertor' },
            ],
          },
        },
      },
      include: { options: true },
    });
    

    I replaced the some filter with the every filter and used OR conditions within it. This should find products that have at least one option with either the specified title and description

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search