PrismaP
Prisma2y ago
3 replies
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
  `;
Was this page helpful?