Hi i've been racking my head on this for days. First here's the schema

// This is your Prisma schema file,
// learn more about it in the docs: <https://pris.ly/d/prisma-schema>

// The Models have been standardized this way to remove any confusion
// in_ : denotes that it is in these related field ex: mtg is in_boxes
// _id : is a field that requires the id of an other table like : user_id
// _rel: This is prisma generated field
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Games {
  id         Int      @id @default(autoincrement())
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  short_hand String   @unique @db.VarChar(255)
  name       String   @unique @db.VarChar(255)
  in_boxes   Box[]
  in_sets    Sets[]
  vendor     String   @db.VarChar(255) @default("")
  sealed     String[] @db.VarChar(255)
}

model User {
  id         Int      @id @default(autoincrement())
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  username   String   @unique @db.VarChar(255)
  password   String   @db.VarChar(255)
  role       Role     @default(USER)
  first_name String?  @db.VarChar(255)
  last_name  String?  @db.VarChar(255)
  stores     Store[]
}

model Store {
  id         Int      @id @default(autoincrement())
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  name       String   @unique @db.VarChar(255)
  owner_id   Int?      
  users_list User[]
  boxes      Box[]
}

model Box {
  id                Int      @id @default(autoincrement())
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt
  set               String   @db.VarChar(255)
  set_rel           Sets     @relation(fields: [set], references: [name])
  box_number        String   @db.VarChar(100)
  box_second_number String?  @db.VarChar(100)
  game_id           Int?     @default(1)
  game_rel          Games?   @relation(fields: [game_id], references: [id])
  store_id          Int?
  store_rel         Store?   @relation(fields: [store_id], references: [id])


}

model Sets {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String   @unique @db.VarChar(255)
  code      String?  @db.VarChar(255)
  game_id   Int?     @default(1)
  game_rel  Games?   @relation(fields: [game_id], references: [id])
  edition   String?
  children  String[]
  in_boxes  Box[]


}

model Logs {
  id         Int      @id @default(autoincrement())
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
  store_id   Int
  user_id    Int      
  action     String   @db.VarChar(255)
  message    String   @db.VarChar(255)
}

enum Role {
  USER
  ADMIN
  CHRIS}

Then here's the query i'm trying to do:

const set = await context.prisma.sets.findFirst({
        where: {
          AND: [
            {
              OR: [
                {
                  name: {
                    contains: name,
                    mode: "insensitive",
                  },
                },
                {
                  code: {
                    contains: code,
                    mode: "insensitive",
                  },
                },
              ],
            },
            {
              in_boxes: {
               every :{
                store_id: {
                  equals: context.req.session.storeId,
                }
               }
              }
            }
          
          ],
          
        },
        select: {
          id: true,
          name: true,
          code: true,
          game_id: true,
          children: true,
          in_boxes: true,
        }
      });

I'm trying to return the set that contains either the name or the code but only return those set who are in the boxes who have that storeId (an Int) The first way I've tried ids by getting all the boxes and then _`set?.in_boxes.filter((box: any) => box.store_id === context.req.session.storeId);` however I soon realised that the more Set request I will get the more slower this will go_ The other way is that i tried ataching the in_boxes to the OR statements however it never returns the right set An other way i've though is to go with my box table to find the set via name or code however i think it would be too cumbersome

Hi Christophe 👋 Here’s the GitHub Gist of the solution for the approach which worked for me. Also added the approach of filtering through box table. Can you have a look?

👋 Hello @Christophe Rudyj, did you have a chance to check Nurul's recommendation? Let us know if it worked for you!