In my quest, I aim to retrieve all TutelageClasses where the user is not yet registered for the upcoming session. However, the current solution showcases all TutelageClasses regardless of registration status.
My attempt involves executing the following query using 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 are my tables: | a (TutelageClass) | b (TutelageSession) | c | d (User) | | :-: | :-: | :-: | :-: | | id | id | #user | id | | title | title | #tutelageSession | name | | #nextSession |
The simplified version of my Prisma schema looks like this:
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])
}
Lastly, the code snippet I am experimenting with:
const c = await client.tutelageClass.findMany({
select: {
id: true,
title: true,
}, where: {
nextSession: {
registeredStudents: {
none: {
id: userIdNotRegistered
}
}
}
}
});