How to cover this custom SQL with Prisma

Hi, i currently use this SQL statement to fetch data and make calculations. Is this also possible without a RAW query?
const result: {
id: string;
name: string;
distance: number;
}[] = await client.$queryRaw`
WITH MaxDistances AS (
SELECT
t.id AS trip_id,
MAX(st.distTraveled) AS max_distance
FROM
Trip t
JOIN
StopTime st ON t.id = st.tripId
GROUP BY
t.id
)
SELECT
a.id AS id,
a.name AS name,
SUM(md.max_distance) AS distance
FROM
Agency a
JOIN
Route r ON a.id = r.agencyId
JOIN
Trip t ON r.id = t.routeId
JOIN
MaxDistances md ON t.id = md.trip_id
GROUP BY
a.id
`;
const result: {
id: string;
name: string;
distance: number;
}[] = await client.$queryRaw`
WITH MaxDistances AS (
SELECT
t.id AS trip_id,
MAX(st.distTraveled) AS max_distance
FROM
Trip t
JOIN
StopTime st ON t.id = st.tripId
GROUP BY
t.id
)
SELECT
a.id AS id,
a.name AS name,
SUM(md.max_distance) AS distance
FROM
Agency a
JOIN
Route r ON a.id = r.agencyId
JOIN
Trip t ON r.id = t.routeId
JOIN
MaxDistances md ON t.id = md.trip_id
GROUP BY
a.id
`;
2 Replies
RaphaelEtim
RaphaelEtim2mo ago
Hi @Nico Schett 👋 This is not possible without using queryRaw as Prisma Client doesn't have support for SQL functions like SUM, MAX etc.
Nico Schett
Nico Schett2mo ago
Okay thanks