Should I stick with raw SQL?
I'm migrating an on old PHP app I wrote 11 years ago to React (Nestjs / Nextjs). In the new stack I'm using Prisma for new calls but I have an old SQL statement I know works. Should I stick with sending it as raw SQL or is there a better way with Prisma?
To be honest I haven't gotten this working yet. Coping and pasting query into my SQL client gives me the results I want but trying to run it though the prisma client I get a BigInt error. But I feel like I should be able to work that out. I have no idea how I would go about joining the tables needed though. I'm completely open to reading docs on how to do so if someone can point me in the right direction.
const columns = `e.date_test_completed AS 'CompletedDate', c.name AS 'courtName', c.number AS 'courtId', COUNT(*) AS 'count', SUM(c.court_fee) AS 'Fee'`;
const query = `SELECT ${columns}
FROM enrollment AS e, student_citation AS st_citation, court AS c, user AS u, student_registration AS student
WHERE e.student_registration_id = st_citation.student_registration_id
AND student.student_registration_id = e.student_registration_id
AND st_citation.court_id = c.court_id
AND u.user_id = student.user_id
AND u.status != 0
AND student.status != 0
AND (e.date_test_completed BETWEEN '${start_date}' AND '${end_date}')
AND Name != 'VOL'
GROUP BY courtName;`;
return JSON.stringify(
this.prisma.$queryRaw`${query}`,
(key, value) => (typeof value === 'bigint' ? value.toString() : value),
);
const columns = `e.date_test_completed AS 'CompletedDate', c.name AS 'courtName', c.number AS 'courtId', COUNT(*) AS 'count', SUM(c.court_fee) AS 'Fee'`;
const query = `SELECT ${columns}
FROM enrollment AS e, student_citation AS st_citation, court AS c, user AS u, student_registration AS student
WHERE e.student_registration_id = st_citation.student_registration_id
AND student.student_registration_id = e.student_registration_id
AND st_citation.court_id = c.court_id
AND u.user_id = student.user_id
AND u.status != 0
AND student.status != 0
AND (e.date_test_completed BETWEEN '${start_date}' AND '${end_date}')
AND Name != 'VOL'
GROUP BY courtName;`;
return JSON.stringify(
this.prisma.$queryRaw`${query}`,
(key, value) => (typeof value === 'bigint' ? value.toString() : value),
);
1 Reply
I think I've mostly solved this by doing
It seems like my primary issuse now is no support to groupBy court.
My models look like this.
return this.prisma.enrollment.findMany({
include: {
student_registration: {
include: {
user: true,
citation: {
include: {
court: true,
},
},
},
},
},
where: {
completedDate: {
gte: `${startDate}`,
lt: `${endDate}`,
},
},
});
return this.prisma.enrollment.findMany({
include: {
student_registration: {
include: {
user: true,
citation: {
include: {
court: true,
},
},
},
},
},
where: {
completedDate: {
gte: `${startDate}`,
lt: `${endDate}`,
},
},
});
model Court {
court_id Int @id @default(autoincrement()) @db.UnsignedInt
courtName String @map("name") @db.VarChar(120)
court_num String? @map("number") @db.VarChar(10)
(...other court fields)
state_fee Decimal? @db.Decimal(5, 2)
court_fee Decimal? @db.Decimal(5, 2)
school_fee Decimal? @db.Decimal(5, 2)
citation Citation[]
@@map("court")
}
model Citation {
student_citation_id Int @id @default(autoincrement())
student_registration_id Int
(...other Citation fields)
court_id Int?
student_registration student_registration? @relation(fields: [student_registration_id], references: [student_registration_id])
court Court? @relation(fields: [court_id], references: [court_id])
@@map("student_citation")
}
model student_registration {
student_registration_id Int @id @default(autoincrement())
user_id Int @unique(map: "user_id")
(...student info fields)
enrollment enrollment[]
user user @relation(fields: [user_id], references: [user_id])
citation Citation[]
}
model user {
user_id Int @id @unique @default(autoincrement())
(... user fields)
student_registration student_registration?
}
model enrollment {
enrollment_id Int @id @default(autoincrement()) @db.UnsignedInt
student_registration_id Int @db.UnsignedInt
course_id Int @db.UnsignedInt
(... enrollment fields)
student_registration student_registration @relation(fields: [student_registration_id], references: [student_registration_id])
}
model Court {
court_id Int @id @default(autoincrement()) @db.UnsignedInt
courtName String @map("name") @db.VarChar(120)
court_num String? @map("number") @db.VarChar(10)
(...other court fields)
state_fee Decimal? @db.Decimal(5, 2)
court_fee Decimal? @db.Decimal(5, 2)
school_fee Decimal? @db.Decimal(5, 2)
citation Citation[]
@@map("court")
}
model Citation {
student_citation_id Int @id @default(autoincrement())
student_registration_id Int
(...other Citation fields)
court_id Int?
student_registration student_registration? @relation(fields: [student_registration_id], references: [student_registration_id])
court Court? @relation(fields: [court_id], references: [court_id])
@@map("student_citation")
}
model student_registration {
student_registration_id Int @id @default(autoincrement())
user_id Int @unique(map: "user_id")
(...student info fields)
enrollment enrollment[]
user user @relation(fields: [user_id], references: [user_id])
citation Citation[]
}
model user {
user_id Int @id @unique @default(autoincrement())
(... user fields)
student_registration student_registration?
}
model enrollment {
enrollment_id Int @id @default(autoincrement()) @db.UnsignedInt
student_registration_id Int @db.UnsignedInt
course_id Int @db.UnsignedInt
(... enrollment fields)
student_registration student_registration @relation(fields: [student_registration_id], references: [student_registration_id])
}