My goal is to get every TutelageClass where the user is not already registered to the next session. The solution displayed at the end always shows every TutelageClass. I'm trying to execute the following query with Prisma :

SELECT a.id, a.title
FROM a
INNER JOIN ON a.nextSession = b.id
INNER JOIN ON b.id = c.tutelageSession
INNER JOIN ON c.user = d.id
WHERE d.name NOT 'VALUE';

Here my tables : | a (TutelageClass) | b (TutelageSession) | c | d (User) | | ------------------- | ----------------------- | ----------------------- | --------- | | id | id | #user | id | | title | title | #tutelageSession | name | | #nextSession | My Prisma schema is the following (simplified) :

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

model TutelageClass {
  id                   String  @id @default(auto()) @map("_id") @db.ObjectId
  title                String

  nextSessionId String? @db.ObjectId
  nextSession TutelageSession?
}

model TutelageSession {
  id         String   @id @default(auto()) @map("_id") @db.ObjectId

  registeredStudentsIDs String[] @db.ObjectId
  tutelageClassId       String   @unique @db.ObjectId

  tutelageClass      TutelageClass @relation(fields: [tutelageClassId], references: [id], onUpdate: Cascade, onDelete: Restrict)
  registeredStudents User[]        @relation("RegisteredStudentsToTutelageSession", fields: [registeredStudentsIDs], references: [id])
}

model User {
  id            String    @id @default(auto()) @map("_id") @db.ObjectId
  name          String?

  registeredToTutelageSessionIDs String[] @db.ObjectId

  registeredToTutelageSession TutelageSession[] @relation("RegisteredStudentsToTutelageSession", fields: [registeredToTutelageSessionIDs], references: [id])
}

And finally, the code I'm trying :

const c = await client.tutelageClass.findMany({
    select: {
        id: true,
        title: true,
    }, where: {
        nextSession: {
            registeredStudents: {
                none: {
                    id: userIdNotRegistered
                }
            }
        }
    }
});

👋 Hey @Rémi, welcome to the community! prisma rainbow I saw your prompt in #orm-help, but to ensure we keep all convos in one thread, I'm replying here directly, and I've removed that message. Our team tries to reply to messages within a few days (if the community doesn't help first), so we'll do our best to address this today or tomorrow asap! Thank you for your patience! 🙏

Do let me know in case I missed something

Hey @Nurul 👋🏻 Thanks for your answer. I have my user already existing and then he register to a session. But when he register, the session still shows up... You did the same as I tried but for some reason it still isn't working for me...

Could you share with me the record which is getting stored in the database for TutelageSession, TutelageClass and User tables? Maybe the issue could be on how the records are created.

👋 Hello @Rémi did you perhaps have a chance to check Nurul's last question? Let us know if you can share the record which is getting stored in the db.

Hey @Rémi, thanks for sharing the models, but I am more interested

on how the records are created

So essentially the create query of these models.

Hey @Nurul 👋🏻 The user is created with the next-auth package. TutelageSession :

const data:  Omit<TutelageSession>, "id"> = ...
const session = await client.tutelageSession.create({
    data
});

And the TutelageClass is for the moment manually inserted (in order to test)

Hey @Rémi 👋 Apologies for the delay in getting back to you. Is this code perhaps open source? I would like to run the code locally to find out the root cause. Can you also try to run this code on a temporary new database without any data? This would help us determine if this indeed is a data issue or not.