P
Prisma•2d ago
Uncle

Help with related filtering with some

With the following schema how do I search for all leases based on the size of a unit? 
model Unit {
  num             String  @id @unique
  building        String
  size            String
  description     String
  leasedPrice     Int?
  advertisedPrice Int
  notes           String?
  unavailable     Boolean @default(false)
  lease           Lease[]

  @@index([num(sort: Desc)])
}
model Lease {
  leaseId              String        @id @unique @default(cuid(2))
  unitNum              String
  price                Int
  leaseCreatedAt       DateTime      @default(now())
  leaseEnded           DateTime?
  dropboxURL           String?
  anvilEID             String?       @unique
  stripeSubscriptionId String?
  invoices             Invoice[]
  unit                 Unit          @relation(fields: [unitNum], references: [num])


  @@unique([leaseId, unitNum, price])
  @@index([leaseId, leaseCreatedAt(sort: Desc)])
}
model Unit {
  num             String  @id @unique
  building        String
  size            String
  description     String
  leasedPrice     Int?
  advertisedPrice Int
  notes           String?
  unavailable     Boolean @default(false)
  lease           Lease[]

  @@index([num(sort: Desc)])
}
model Lease {
  leaseId              String        @id @unique @default(cuid(2))
  unitNum              String
  price                Int
  leaseCreatedAt       DateTime      @default(now())
  leaseEnded           DateTime?
  dropboxURL           String?
  anvilEID             String?       @unique
  stripeSubscriptionId String?
  invoices             Invoice[]
  unit                 Unit          @relation(fields: [unitNum], references: [num])


  @@unique([leaseId, unitNum, price])
  @@index([leaseId, leaseCreatedAt(sort: Desc)])
}
I get an error when I do 
   const leases = await prisma.lease.findMany({
      where: {
         unit: {
            some: {
               size: '04x06'
            }
         }
      }
   })
   const leases = await prisma.lease.findMany({
      where: {
         unit: {
            some: {
               size: '04x06'
            }
         }
      }
   })
1 Reply
Prisma AI Help
Prisma AI Help•2d ago
You opted for speed from this bot. Let's conjure up an answer right now! To search for all leases based on the size of a unit using the schema you provided, you need to adjust your query slightly. The error you're encountering is because you're using some on a one-to-one relation, which is not correct. Instead, you should directly filter on the unit relation. Here's the correct way to do it: 
const leases = await prisma.lease.findMany({
  where: {
    unit: {
      size: '04x06'
    }
  }
})
const leases = await prisma.lease.findMany({
  where: {
    unit: {
      size: '04x06'
    }
  }
})
This query will return all leases where the associated unit has a size of '04x06'. The reason for this change is that in your schema, the relationship between Lease and Unit is a many-to-one relationship (many leases can belong to one unit). When filtering on such a relationship, you don't use some, every, or none. These operators are used for one-to-many or many-to-many relationships. For more information on filtering relations in Prisma, you can refer to the Prisma documentation on filtering and sorting. Prisma documentation on filtering and sorting provides more examples and explanations on how to filter based on related records. ✅ Great, thanks for letting us know that this solved your question!

Did you find this page helpful?