Search code examples
mysqlnode.jsprisma

Prisma nested query

I've a query

SELECT COUNT(*) AS mock_test_count 
FROM mock_test 
WHERE isActive = 1 
AND id NOT IN ( SELECT mock_test_id 
                FROM mock_tests_given_by_students 
                WHERE student_id = 2 )

I'm using prisma orm.

already gone through the docs of nested query in prisma, but didn't found any solution. I can solve the query using 2 different prisma query, but I want to it in a single prisma function. Is it even possible via prisma

Solution

  • You can rewrite it without a subquery:

    SELECT COUNT(*) AS mock_test_count 
FROM mock_test a
LEFT JOIN mock_tests_given_by_students b
  ON b.mock_test_id = a.id
  AND b.student_id = 2
WHERE a.isActive = 1 
AND b.mock_test_id IS NULL