P
Prisma•6mo 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•6mo 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•6mo ago
Okay thanks
Want results from more Discord servers?
Add your server