javascript - Prisma js ORM - how to filter for results that have entry in a related table (effectively JOIN)? - Stack Overflow

admin管理员组

文章数量:1345884

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

Share Improve this question Follow asked Mar 16, 2022 at 15:57 ilmoiilmoi 2,56444 gold badges3535 silver badges5757 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 6

      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,
   },
 });

本文标签：