Compare a field with a field in a related table in Prisma ORM

Question

I am trying to filter results by comparing one related table field with another related table field.

(simplified situation)
At our institute we offer Courses that contain Lessons where a Teacher can be assigned to.
Courses are provided by Departments.
Teachers are from a Department.
We want to find all Lessons where the Teachers is from another Department than the Course that the Lesson is part of.

In SQL this can be achieved like this:

SELECT
  * 
FROM
  Lesson l
JOIN Teacher t ON
  t.id = l.teacher_id
JOIN Course c ON
  c.id = l.course_id
WHERE 
  c.department_id <> t.department_id 

Currently I use a raw query using Prisma.sql, but I would like to use Prisma client API. I tried something like this:

return await prisma.lesson.findMany({
  where: {
    Teacher: {
      department_id: { equals: ????? }
    }
  }
})

But I have no idea what to place at the ?????.

Is it possible to achieve this kind of filter using the Prisma client API?

Answer 1

This is not currently supported: https://github.com/prisma/prisma/issues/15068.

A new preview feature is running for comparing columns in the same table (https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#compare-columns-in-the-same-table), that also triggered the discussion to find a resolution for your issue.