Hi all I am trying to make the sum of some culmns but withou Prisma #orm-help

Hi all, I am trying to make the sum of some culmns...

Alban Kaperi

08/17/2022, 6:11 PM

Hi all, I am trying to make the sum of some culmns but without success, here is the idea:

Copy code

const customers = await prisma.employee.findMany({
    include: {
        workingHours: true,
    },
})

output:

Copy code

[
    {
        "id": 1,
        "name": "beth",
        "surname": "halvorson",
        "gender": "Female",
        "email": "<mailto:bethhalvorson@gmail.com|bethhalvorson@gmail.com>",
        "phone": "501-945-084",
        "code": "kn7v6",
        "points": 12941,
        "workingHours": [
            {
                "id": 1,
                "normalTime": 7,
                "overTime": 1,
                "doubleTime": 1,
                "vacationTime": 0,
                "sickTime": 0,
                "employeeId": 1,
            },
            {
                "id": 2,
                "normalTime": 4,
                "overTime": 3,
                "doubleTime": 1,
                "vacationTime": 0,
                "sickTime": 0,
                "employeeId": 1,
            },
        ]
    },
]

I want to change the query to receive smth like this:

Copy code

[
    {
        "id": 1,
        "name": "beth",
        "surname": "halvorson",
        "gender": "Female",
        "email": "<mailto:bethhalvorson@gmail.com|bethhalvorson@gmail.com>",
        "phone": "501-945-084",
        "code": "kn7v6",
        "points": 12941,
        "_sum": {
            "totalHours": 18,
        },
    },
]

where _sum: is the total of all columns(normalTime, overTime, vacationTime, sickTime) I didn't want to use javascript methods to achieve that

✅ 1

👀 2

Austin

08/17/2022, 9:01 PM

Hey there 👋, You could take a look at our

aggregate

API (docs), specifically the

_sum

option. If you provided your

schema.prisma

, I could attempt to create a query for you.

Alban Kaperi

08/22/2022, 11:37 AM

Copy code

model WorkingHour {
  id           Int       @id @default(autoincrement())
  normalTime   Float     @default(0)
  overTime     Float     @default(0)
  doubleTime   Float     @default(0)
  vacationTime Float     @default(0)
  sickTime     Float     @default(0)
  Employee     Employee? @relation(fields: [employeeId], references: [id])
  employeeId   Int?
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @default(now())
}

model Employee {
  id            Int       @id @default(autoincrement())
  name          String
  surname       String
  gender        String?
  date_of_birth DateTime?

  email String? @unique
  phone String?
  code  String? @unique

  total_sale Float @default(0)
  points     Int   @default(0)

  purchases    Purchase[]
  sales        Sale[]
  salary       Salary[]
  workingHours WorkingHour[]

  address      Address? // this doesnt need any values in the seeder
  Department   Department? @relation(fields: [departmentId], references: [id])
  departmentId Int?

  createdAt DateTime @default(now()) // its also date he started working
  updatedAt DateTime @updatedAt

  JobTitleHistory JobTitleHistory[]

  EmploymentTerms EmploymentTerms?
  JobTitle        JobTitle?        @relation(fields: [jobTitleId], references: [id])
  jobTitleId      Int?
  Salaryconfig    Salaryconfig?
}

Alban Kaperi

08/22/2022, 11:37 AM

sorry for not providing the schema before

Austin

08/22/2022, 7:44 PM

No problem! Did you have a chance to check out the

aggregate

docs?

Alban Kaperi

08/23/2022, 11:40 AM

yeap i did. I did find 2 solutions: • with raw queries • and to post process it with javascript (eg. with map)

🚀 1

Alban Kaperi

08/23/2022, 11:41 AM

But with aggregate, cant seem to find one

3 Views

Open in Slack

Previous Next