how to assemble a sql query to return the following data?

Question

i have the following schema of database in prisma and mysql:


model User {
id      Int   @default(autoincrement()) @id
name    String
email   String @unique 
password String
massiva Massive[]
city_alert City_alert[]
status String @default("Normal")
department Departments @relation(fields: [department_id], references: [id])
department_id Int
user_in_city User_in_city[]
user_in_work User_in_work[]
historic_pausa Historic_pause[]
client_massive Client_massive[]
coffee Coffee[]
services  Services[] @relation("suport")
services_user Services[] @relation("user")
created_at DateTime @default(now())
updated_at DateTime @updatedAt
}

model Services {
  id      Int   @default(autoincrement()) @id
  protocol String
  incident incidentType @relation( fields: [incident_id], references: [id])
  incident_id Int
  status String
  suport User @relation(name: "suport", fields: [suport_id], references: [id])
  suport_id Int
  user User @relation(name: "user", fields: [user_id], references: [id])
  user_id Int
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
}


model incidentType  {
  id      Int   @default(autoincrement()) @id
  name String
  value Int
  services Services[]
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
}

but I need to get the following data:

[
[user, 
quantities_of_services_per_incientType_1,
quantities_of_services_per_incientType_1,
quantities_of_services_per_incientType_1
],
[user2, 
quantities_of_services_per_incientType_1,
quantities_of_services_per_incientType_1,
quantities_of_services_per_incientType_1
]]

exemple: 
[
[jhon, 0,4,3,5],
[Maria, 0,3,4,3],
]

I have a user table and each user has several services, each service belonging to an incent, I need to bring the amount of service for each user per incident, even if a user does not have services for a given incident

how do i do this with raw sql or with prisma query?

how to assemble a sql query to return the following data?

0 Answers0