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

Question

select * 
from WeeklyChallengeCourses 
where weekly_challenge_id = (select weekly_challenge_id 
                             from WeeklyChallengeCourses 
                             where course_id = 210);

Result will be the below selected one:

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

Answer 1

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.