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 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")
}

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

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

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.

message.txt8.81KB

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 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")
}

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

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

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

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.

message.txt8.81KB

Count with distinct

Count with distinct

Similar Threads

Similar Threads

Similar Threads