How to retrieve a sub query in prisma client with same table?

user_14207423 0
Viewed 1698
select * 
from WeeklyChallengeCourses 
where weekly_challenge_id = (select weekly_challenge_id 
                             from WeeklyChallengeCourses 
                             where course_id = 210);

Result will be the below selected one:

enter image description here

const data = await context.prisma.weeklyChallengeCourses.findMany({
        where:{
            weekly_challenge_id: {
                ..............
            }
        },
    });
prisma-graphql prisma node.js postgresql
1 Answers

In Prisma, you would have to use two different queries to solve this:

  1. Run an equivalent of the subquery to fetch the weekly_challenge_id
  2. Run a findMany with the weekly_challenge_id found in step 1.
// I'm assuming course_id is unique. 
const course = await context.prisma.findUnique({ where: { course_id: 210 } }); 

const data = await context.prisma.weeklyChallengeCourses.findMany({
        where:{
            weekly_challenge_id: course.weekly_challenge_id
        },
    });

Alternatively, you could use the rawQuery feature to run the SQL directly and do it in one query.

user_88558440
Related