Prisma Client Select query on existence of value in joined table via Schema-CodePudding

In my instance i have a schema joining bonuses to a casino. query works great for data but I am unable to filter via the query itself. The where clause I used appears to be correct but I get an error the stating Object literal may only specify known properties, and 'nodeposit' does not exist in type. But I can query that data.

const data = await prisma.casino_p_casinos.findMany({
where: { 
  approved: 1, 
  rogue: 0,
  bonuses: {
    nodeposit: { gt : 0 },
  }
 },
select: {
  id: true,
  clean_name: true,
  casino: true,
  button: true,
  bonuses: {
    where: {
      nodeposit: { gt: 0 },
    },
  },
},

take: 14,

});

If I remove the bonus pard in the WHERE clause the query works as expected but I want to grab all bonuses for each casino, but only if the bonuses contains a nodeposit value.

This nis what I want to use.

    const data = await prisma.casino_p_casinos.findMany({
where: { 
  approved: 1, 
  rogue: 0,
  bonuses: {
    nodeposit: { gt : 0 },
  },
 },
select: {
  id: true,
  clean_name: true,
  casino: true,
  button: true,
  bonuses: true,
},

take: 14,

});

SCHEMA :

model casino_p_casinos {
  id            Int                             @id @default(autoincrement())
  casino        String?
  type          String?
  url           String?
  bonuses       casino_p_bonus[]

model casino_p_bonus {
  id               Int              @id @default(autoincrement())
  parent           Int
  game             String?
  freespins        Int?
  freeplay         String?
  nodeposit        Int?
  deposit          Int?
 
  casino_p_casinos casino_p_casinos @relation(fields: [parent], references: [id])
}

CodePudding user response：

You have a one to many relation, so when you add a where clause, you have one more layer with some, every or none like

const data = await prisma.casino_p_casinos.findMany({
      where: {
        approved: 1,
        rogue: 0,
        bonuses: {
          // 'some' can be replaced by 'every' or 'none' here
          some: {
            nodeposit: { gt: 0 }
          }
        }
      },
      select: {
        id: true,
        clean_name: true,
        casino: true,
        button: true,
        bonuses: true
      },
      take: 14
    })

This query will filter casinos where some nodeposit are greater than 0 and return all bonuses, even those who are equals to 0.

And then, if you only want bonuses with nodeposit greater than 0 in casinos that have some, you should do:

const data = await prisma.casino_p_casinos.findMany({
      where: {
        approved: 1,
        rogue: 0,
        bonuses: {
          // 'some' can be replaced by 'every' or 'none' here
          some: {
            nodeposit: { gt: 0 }
          }
        }
      },
      select: {
        id: true,
        clean_name: true,
        casino: true,
        button: true,
        bonuses: {
          where: {
            nodeposit: { gt: 0 }
          }
        }
      },
      take: 14
    })