I have 2 tables:

model Collection {
    id                String                 @id @default(uuid()) @db.Uuid/
    floorPrices       CollectionFloorPrice[]
}

model CollectionFloorPrice {
    id           String     @id @default(uuid()) @db.Uuid
    collection   Collection @relation(fields: [collectionId], references: [id])
    collectionId String     @db.Uuid
}

How do I query collections that only have rows present in CollectionFloorPrice? In SQL it would be a simple JOIN.

This isn't working:

    return await this.prisma.collection.findMany({
      where: {
        floorPrices: {
          exists: true,
        },
      },
    });

I have 2 tables:

model Collection {
    id                String                 @id @default(uuid()) @db.Uuid/
    floorPrices       CollectionFloorPrice[]
}

model CollectionFloorPrice {
    id           String     @id @default(uuid()) @db.Uuid
    collection   Collection @relation(fields: [collectionId], references: [id])
    collectionId String     @db.Uuid
}

How do I query collections that only have rows present in CollectionFloorPrice? In SQL it would be a simple JOIN.

This isn't working:

    return await this.prisma.collection.findMany({
      where: {
        floorPrices: {
          exists: true,
        },
      },
    });

• javascript
• orm
• prisma

2 Answers 2

      prisma.collection.findMany({
        where: { floorPrices: { some: {} } }
      })

Prisma's relation filters for a model named CollectionFloorPrice are:

export type CollectionFloorPriceFilter = {
  every?: CollectionFloorPriceWhereInput | null
  some?: CollectionFloorPriceWhereInput | null
  none?: CollectionFloorPriceWhereInput | null
}

To get only Collections that have at least one CollectionFloorPrice, you should use some (instead of exists) and specify a condition that always return true for any related record that exists.

And if you want to your query includes related CollectionFloorPrices you must specify it in include property.

 return await this.prisma.collection.findMany({
   where: {
     floorPrices: {
       some: {
         id: { not: "" } // It always should be true.
       },
     },
   },
   
   // if you want to include related floor prices in returned object:
   include: {
     floorPrices: true,
   },
 });