Unknown argument 'ID' in double nested find many with one to many relation
Hi I have been recently refactoring a bunch of my more complicated queries to no longer use query raw unsafe and I have ran into an issue I have been unable to find any resources on.
Any help would be greatly appreciated I will post more details as I cant fit everything into the post.
1 Reply
Prisma query:
const data = await prisma.heirarchy.findMany({
skip: Number(start),
take: Number(length),
orderBy: [
junior: {
Synergy_responses: {
ID: "asc"
}
}
],
where: { AND: [ {deleted_at: null}, {seniorID: res.locals.user.userID}, { OR: [ {junior: {Synergy_Responses: {ID: {contains: search.value}}}}, {junior: {Synergy_Responses: {userID: {contains: search.value}}}}, ] } ]
}, include: { junior: { include: { Synergy_Responses: true } } } }) Raw Unsafe Version: const data = await prisma.$queryRawUnsafe(
where: { AND: [ {deleted_at: null}, {seniorID: res.locals.user.userID}, { OR: [ {junior: {Synergy_Responses: {ID: {contains: search.value}}}}, {junior: {Synergy_Responses: {userID: {contains: search.value}}}}, ] } ]
}, include: { junior: { include: { Synergy_Responses: true } } } }) Raw Unsafe Version: const data = await prisma.$queryRawUnsafe(
SELECT sr.*
FROM Heirarchy h
JOIN Synergy_Responses sr ON h.juniorID = u.userID
WHERE h.seniorID = ${uid}
and
deleted_at IS NULL
AND
(sr.ID LIKE ${sv}
OR sr.score LIKE ${sv}
OR sr.percentage LIKE ${sv}
OR sr.userID LIKE ${sv}
OR sr.created_at LIKE ${sv}
OR sr.updated_at LIKE ${sv})
ORDER BY ${column} ${dir}
LIMIT ${length} OFFSET ${start};
);
Referenced schema:
model Heirarchy {
ID String @id @default(cuid())
senior Users? @relation(name: "senioruserinfo", fields: [seniorID], references: [userID], onDelete: Cascade, onUpdate: Cascade)
seniorID String?
junior Users? @relation(name: "junioruserinfo", fields: [juniorID], references: [userID], onDelete: Cascade, onUpdate: Cascade)
juniorID String?
depth Int
org Orgs? @relation(fields: [orgID], references: [orgID], onDelete: Cascade, onUpdate: Cascade)
orgID String?
created_at DateTime @default(now())
updated_at DateTime @updatedAt
deleted_at DateTime?
}
model Users {
userID String @id @unique @default(cuid())
email String @unique
username String @default("placeholder")
passhash String
marketing Boolean @default(false)
policy Boolean @default(false)
group Usergroups? @relation(fields: [groupID], references: [groupID])
groupID String?
seniors Heirarchy[] @relation("junioruserinfo")
juniors Heirarchy[] @relation("senioruserinfo")
org Orgs? @relation(fields: [orgID], references: [orgID], onDelete: Cascade, onUpdate: Cascade)
orgID String?
sessions Session[]
passResetToken String? @db.LongText
passResetExpire DateTime?
magicLinkToken String? @db.LongText
magicLinkExpire DateTime?
inviteToken String? @db.LongText
created_at DateTime @default(now())
updated_at DateTime @updatedAt
deleted_at DateTime?
Synergy_Responses Synergy_Responses[]
Mood_Tracker Mood_Tracker[]
Licenses Licenses[]
}
model Synergy_Responses {
ID String @id @default(cuid())
response_data Json
score Int
percentage Int
synergy Synergy? @relation(fields: [synID], references: [ID])
synID String
user Users? @relation(fields: [userID], references: [userID])
userID String?
created_at DateTime @default(now())
updated_at DateTime @updatedAt
deleted_at DateTime?
}
Error:
→ 225 const data = await prisma.heirarchy.findMany({
skip: 0,
take: 10,
orderBy: [
{
junior: {
Synergy_Responses: {
orderBy: {
ID: "asc"
}
}
}
}
],
where: {
AND: [
{
deleted_at: null
},
{
seniorID: "clvwspk72000g13p54owy7cuf"
},
{
OR: [
{
junior: {
Synergy_Responses: {
ID: {
contains: ""
}
}
}
},
{
junior: {
Synergy_Responses: {
userID: {
contains: ""
}
}
}
}
]
}
]
},
include: {
junior: {
include: {
Synergy_Responses: true
}
}
}
})
This issue is no longer high priority as I have fixed the problem by splitting the query into 2 however I would still be curious to see a more elegant solution as I am really enjoying using prisma and would love to learn more.