NestJS Prisma ORM. Select specific fields

Question

There are two tables sim_card and observation 1 to 1. It is necessary to select all SIM cards with the status Active (true) and which are not used in the observation table. (That is, an observation card is created and a free and active SIM card is issued to it.)

model observation {
  id         Int       @id @unique @default(autoincrement()) @map("observation_id")
  number     Int
  contract   String?
  sim_card   sim_card? @relation(fields: [sim_cardId], references: [id], map: "ObservationId_sim_card_key")
  sim_cardId Int?      @unique(map: "ObservationId_sim_card_key")
  firmId     Int?      @unique(map: "Observation_firmId_key")
  client     client?   @relation(fields: [firmId], references: [id], map: "Observation_firmId_key")
}


model sim_card {
  id          Int          @id @default(autoincrement())
  number      String       @unique(map: "Sim_card_number_key")
  operator    String       @default("kyivstar")
  active      Boolean      @default(true)
  busy        Boolean      @default(false)
  observationId Int?
}

As I imagine, (but it doesn't work)

findActive(): Promise {
        return Promise
            .resolve(this.prismaService.sim_card
                .findMany({
                    where: {
                        active: true
                    },
                    include: {
                        observation: {
                            select: {
                                sim_card: false
                            }
                        }
                    }
                }))
            .catch((error) => {
                throw new HttpException(error.message, HttpStatus.BAD_REQUEST);
            })
    }

Shea Hunter Belsky · Answer

If you want to select any SIM Card that is not in the observations table, why not search for SIM cards where observationId is absent?

this.prismaService.sim_card.findMany({
    where: {
        active: true,
        observationId: { equals: null }
    },
}))

observationId will be null if a given sim_card row is not connected to any observation rows. Let me know if I didn't understand you correctly, but at the end of the day if you are trying to exempt rows, it needs to be added to your where filter. select and include are only used AFTER the initial filtering has been done - select and include just determine what fields on the already-filtered rows get returned.

NestJS Prisma ORM. Select specific fields

1 Answers