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),
    );

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.

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),
    );

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),
    );

Should I stick with raw SQL?

Similar Threads

Should I stick with raw SQL?

Similar Threads

Similar Threads

Similar Threads