PrismaP
Prisma•2y ago•
2 replies
Rev

Count with distinct

The scenario is: I have many inventories that are associated with a single tree (Many to One).

model Inventory {
  id     Int    @id @default(autoincrement())
  Tree   Tree?  @relation(fields: [treeId], references: [id])
  treeId Int?                       

  @@map("TB_INVENTORY")
}

model Tree {
  id                Int         @id @default(autoincrement())
  HistoricInventory Inventory[]

  @@map("TB_TREE")
}


I have a query that selects the inventories from the database.

obs: This is a partial query; there are more than 300 lines of select and where clauses.

return await this.prisma.inventory.findMany({
  distinct: 'treeId',
  orderBy,
  skip: Number(skip) || 0,
  take: Number(limit),
  where: {
    status: {
      notIn: [
        InventoryStatusEnum.INATIVO,
        InventoryStatusEnum.REMOVED,
        InventoryStatusEnum.PENDING,
      ],
    },
  },
});


I use distinct along with orderBy to ensure I only get the latest inventory from each tree, not a random one.

What is the problem?
After I get the inventories, I need to count them for pagination purposes.

The only way I found to count them is to select every inventory in the database grouped by treeId and use .length() to count.

return await this.prisma.inventory.groupBy({
  by: ['treeId'],
  where: {
    status: {
      notIn: [
        InventoryStatusEnum.INATIVO,
        InventoryStatusEnum.REMOVED,
        InventoryStatusEnum.PENDING,
      ],
    }
  }
});


Is there any easier way to perform this count? I thought of something like COUNT DISTINCT, but I can't use raw queries because I have 295 lines of conditional where clauses.
image.png
image.png
message.txt8.81KB
Was this page helpful?