How to filter on relation in Prisma ORM

Question

I am working currently on a course service. Users have the possibility to register and deregister for courses. The entire system is built in a microservice architecture, which means that users are managed by another service. Therefore, the data model of the course service looks like this:

model course {
  id                Int                @id @default(autoincrement())
  orderNumber       Int                @unique
  courseNumber      String             @unique @db.VarChar(255)
  courseName        String             @db.VarChar(255)
  courseOfficer     String             @db.VarChar(255)
  degree            String             @db.VarChar(255)
  ectCount          Int
  faculty           String             @db.VarChar(255)
  isWinter          Boolean            @default(false)
  isSummer          Boolean            @default(false)
  courseDescription String?            @db.VarChar(255)
  enrollmentCourse  enrollmentCourse[]
}

model enrollmentCourse {
  id       Int    @id @default(autoincrement())
  userId   String @db.VarChar(1024)
  course   course @relation(fields: [courseId], references: [id])
  courseId Int
}

I want to find all the courses in which a certain user has enrolled. I have written 2 queries. One goes over the courses and tries to filter on the enrollmentCourse. However, this one does not work and I get all the courses back. Whereas the second one goes over the enrollmentCourse and then uses a mapping to return the courses. This works, but I don't like this solution and would prefer the 1st query if it worked: (I have used this guide in order to write the first query: here)

const result1 = await this.prisma.course.findMany({
  where: { enrollmentCourse: { every: { userId: user.id } } },
  include: { enrollmentCourse: true }
});

console.log('Test result 1: ');
console.log(result1);

const result2 = await this.prisma.enrollmentCourse.findMany({
  where: { userId: user.id },
  include: { course: { include: { enrollmentCourse: true } } }
});

console.log('Test result 2: ');
console.log(result2.map((enrollment) => enrollment.course));

If now the user is not enrolled in a course the result of both queries are:

Test result 1:
[
  {
    id: 2,
    orderNumber: 1,
    courseNumber: 'test123',
    courseName: 'testcourse',
    courseOfficer: 'testcontact',
    degree: 'Bachelor',
    ectCount: 5,
    faculty: 'testfaculty',
    isWinter: true,
    isSummer: false,
    courseDescription: 'test.pdf',
    enrollmentCourse: []
  }
]
Test result 2:
[]

If now the user has enrolled courses it looks like this:

Test result 1:
[
  {
    id: 2,
    orderNumber: 1,
    courseNumber: 'test123',
    courseName: 'testcourse',
    courseOfficer: 'testcontact',
    degree: 'Bachelor',
    ectCount: 5,
    faculty: 'testfaculty',
    isWinter: true,
    isSummer: false,
    courseDescription: 'test.pdf',
    enrollmentCourse: [ [Object] ]
  }
]
Test result 2:
[
  {
    id: 2,
    orderNumber: 1,
    courseNumber: 'test123',
    courseName: 'testcourse',
    courseOfficer: 'testcontact',
    degree: 'Bachelor',
    ectCount: 5,
    faculty: 'testfaculty',
    isWinter: true,
    isSummer: false,
    courseDescription: 'test.pdf',
    enrollmentCourse: [ [Object] ]
  }
]

As we can see the first query does not work correctly. Can anybody give me a hint? Is there anything that I'm missing?

Answer 1

As per the doc you mentioned, you need to use some instead of every as you need at least one user returned if it matches.

const result1 = await this.prisma.course.findMany({
  where: { enrollmentCourse: { some: { userId: user.id } } },
  include: { enrollmentCourse: true }
});

This should give all the courses where the user is registered