P
Prisma•3mo ago
Nico Schett

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