PrismaP
Prisma•2y ago•
4 replies
darkTower

Should I stick with raw SQL?

I'm migrating an on old PHP app I wrote 11 years ago to React (Nestjs / Nextjs). In the new stack I'm using Prisma for new calls but I have an old SQL statement I know works. Should I stick with sending it as raw SQL or is there a better way with Prisma?

const columns = `e.date_test_completed AS 'CompletedDate', c.name AS 'courtName', c.number AS 'courtId', COUNT(*) AS 'count', SUM(c.court_fee) AS 'Fee'`;
    const query = `SELECT ${columns}
     FROM enrollment AS e, student_citation AS st_citation, court AS c, user AS u, student_registration AS student
     WHERE e.student_registration_id = st_citation.student_registration_id
                AND student.student_registration_id = e.student_registration_id
                AND st_citation.court_id = c.court_id
                AND u.user_id = student.user_id
                AND u.status != 0
                AND student.status != 0 
                AND (e.date_test_completed BETWEEN '${start_date}' AND  '${end_date}')
                AND Name != 'VOL'
                GROUP BY courtName;`;

 return JSON.stringify(
      this.prisma.$queryRaw`${query}`,
      (key, value) => (typeof value === 'bigint' ? value.toString() : value),
    );

To be honest I haven't gotten this working yet. Coping and pasting query into my SQL client gives me the results I want but trying to run it though the prisma client I get a BigInt error. But I feel like I should be able to work that out. I have no idea how I would go about joining the tables needed though. I'm completely open to reading docs on how to do so if someone can point me in the right direction.
Was this page helpful?