P
Prisma•8mo ago
schettn

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
RaphaelEtim•8mo 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.
schettn
schettnOP•8mo ago
Okay thanks

Did you find this page helpful?