I'm trying to simulate this query:

select * from DamFile
where deletedAt is null
and (filterSize between 1 and 199 or filterSize between 200 and 799 or filterSize >= 800);

With this JS Object:

AND: [
   { OR: [{ AND: [{ filterSize: { gt: 0 } }, { filterSize: { lte: 199 } }] }] },
   { OR: [{ AND: [{ filterSize: { gte: 200 } }, { filterSize: { lte: 799 } }] }] },
   { OR: [{ filterSize: { gte: 800 } }] },
],

Unfortunately I'm not getting any results. Am I able to do complex queries like this using nested conditionals? Thanks

Yes it should work. Could you share your schema so that I can check?

This is the related Model:

model DamFile {
  id                Bytes     @id @db.Binary(26)
  parentId          Bytes?    @db.Binary(26)
  name              String    @db.VarChar(191)
  description       String?   @db.LongText
  comments          String?   @db.LongText
  copyright         String?   @db.VarChar(191)
  filename          String?   @db.VarChar(191)
  filesize          Int?      @default(0) @db.UnsignedInt
  extension         String?   @db.VarChar(5)
  md5               String?   @db.VarChar(32)
  metaData          Json?     @db.Json
  geoData           Json?     @db.Json
  boxId             String?   @db.VarChar(50)
  url               String?   @db.VarChar(191)
  completed         Int?      @default(0) @db.UnsignedTinyInt
  permDelete        Int?      @default(0) @db.UnsignedTinyInt
  filterType        String    @db.VarChar(10)
  filterCountry     String?   @db.VarChar(50)
  filterOrientation String?   @db.VarChar(10)
  filterSize        Int?      @default(0) @db.UnsignedInt
  filterDpi         Int?      @default(0) @db.UnsignedSmallInt
  createdAt         DateTime  @default(now())
  updatedAt         DateTime? @updatedAt
  deletedAt         DateTime?

  // relations
  type   DamFileType      @relation(fields: [typeId], references: [id])
  typeId Bytes            @db.Binary(26)
  mime   DamFileMimeType? @relation(fields: [mimeId], references: [id])
  mimeId Bytes?           @db.Binary(26)
  user   User             @relation(fields: [userId], references: [id])
  userId Bytes            @db.Binary(26)

  DamImages      DamImage[]
  DamFileTags    DamFileTag[]
  DamImageColors DamImageColor[]
}

This should work:

await prisma.damFile.findMany({
    where: {
      AND: [
        { deletedAt: { not: null } },
        {
          OR: [
            { filterSize: { gte: 1, lte: 199 } },
            { filterSize: { gte: 200, lte: 799 } },
            { filterSize: { gte: 800 } },
          ],
        },
      ],
    },
  })

Although I hope these aren’t the actual values you’re querying for 🙂

I'm just trying to get a working solution. I have a function with creates the where object and so far I haven't been able to make the correct arrangement for this particular column.

Prisma query generated (same as what you’d want):

Ok, I see you put the deletedAt inside the AND, have other columns outside of that currently. I'm going to have to change my code 😆. Let me get back to you on the result.