Fix a query
is there something wrong with this query, it isn't working?
await prisma.collection.findFirst({
where: {
id: collectionId,
},
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: {
collection: true,
},
where: {
AND: [
...(surah
? [
{
extraData: {
path: ['surah'],
equals: surah,
},
},
]
: []),
...(verse
? [
{
extraData: {
path: ['verse'],
equals: verse,
},
},
]
: []),
],
},
},
},
})
await prisma.collection.findFirst({
where: {
id: collectionId,
},
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: {
collection: true,
},
where: {
AND: [
...(surah
? [
{
extraData: {
path: ['surah'],
equals: surah,
},
},
]
: []),
...(verse
? [
{
extraData: {
path: ['verse'],
equals: verse,
},
},
]
: []),
],
},
},
},
})
10 Replies
the conditioinal verse isn't working.
surah conditional works
Hey @Anas Badran
If you just add the verse condition and not the surah condition, does the verse condition work?
Can you enable logging to get the underlying raw SQL query and check if it looks as expected?
https://www.prisma.io/docs/orm/prisma-client/observability-and-logging/logging
Logging | Prisma Documentation
Learn how to configure Prisma Client to log the raw SQL queries it sends to the database and other information.
No, it doesn't
I'm about to change the schema
the schema:
The updated query:
it works for surah alone, or verse alone, but not both together.
model Post {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
title String
title_ Json? @db.JsonB
content String
content_ Json? @db.JsonB
collectionId String
collection Collection @relation(fields: [collectionId], references: [id], onDelete: Cascade)
metadata PostMetadata? @relation(name: "PostMetadata")
// extraData Json? @db.JsonB
extraData PostExtraData[]
extension PostExtensionEnum @default(BASE)
tags Tag[] @relation(name: "PostTags")
order Int?
deletedAt DateTime?
}
model PostExtraData {
id String @id @default(cuid())
key String
value String
postId String
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
}
model Post {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
title String
title_ Json? @db.JsonB
content String
content_ Json? @db.JsonB
collectionId String
collection Collection @relation(fields: [collectionId], references: [id], onDelete: Cascade)
metadata PostMetadata? @relation(name: "PostMetadata")
// extraData Json? @db.JsonB
extraData PostExtraData[]
extension PostExtensionEnum @default(BASE)
tags Tag[] @relation(name: "PostTags")
order Int?
deletedAt DateTime?
}
model PostExtraData {
id String @id @default(cuid())
key String
value String
postId String
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
}
await prisma.collection.findFirst({
where: {
id: collectionId,
},
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: {
collection: true,
extraData: true,
},
where: {
extraData: {
some: {
...(verse && surah
? {
AND: [
{
AND: [
{
key: {
equals: 'verse',
},
value: {
equals: verse,
},
},
],
},
{
AND: [
{
key: {
equals: 'surah',
},
value: {
equals: surah,
},
},
],
},
],
}
: surah
? { key: 'surah', value: surah }
: verse
? { key: 'verse', value: verse }
: {}),
},
},
},
},
},
})
await prisma.collection.findFirst({
where: {
id: collectionId,
},
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: {
collection: true,
extraData: true,
},
where: {
extraData: {
some: {
...(verse && surah
? {
AND: [
{
AND: [
{
key: {
equals: 'verse',
},
value: {
equals: verse,
},
},
],
},
{
AND: [
{
key: {
equals: 'surah',
},
value: {
equals: surah,
},
},
],
},
],
}
: surah
? { key: 'surah', value: surah }
: verse
? { key: 'verse', value: verse }
: {}),
},
},
},
},
},
})
Do you mind elaborating your usecase on what you are trying to achieve?
Based on the query, it looks like you could either receive surah or verse or both of them together, right? And based on what parameters you receive, you want to apply the filter?
Yes, you are right
I'm using nextjs, and apply the filters based on what I recieve in searchParams
I tried this also and it didn't work:
await prisma.collection.findFirst({
where: {
id: collectionId,
},
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: {
collection: true,
extraData: true,
},
where: {
extraData: {
some: {
...(verse && surah
? {
AND: [
{ key: { equals: 'verse' }, value: { equals: verse } },
{ key: { equals: 'surah' }, value: { equals: surah } },
],
}
: surah
? { key: 'surah', value: surah }
: verse
? { key: 'verse', value: verse }
: {}),
},
},
},
},
},
})
await prisma.collection.findFirst({
where: {
id: collectionId,
},
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: {
collection: true,
extraData: true,
},
where: {
extraData: {
some: {
...(verse && surah
? {
AND: [
{ key: { equals: 'verse' }, value: { equals: verse } },
{ key: { equals: 'surah' }, value: { equals: surah } },
],
}
: surah
? { key: 'surah', value: surah }
: verse
? { key: 'verse', value: verse }
: {}),
},
},
},
},
},
})
Would something like this work then?
This is a bit verbose, but makes it easy to understand and debug
let result;
if (verse && surah) {
// Condition 1: Both verse and surah are provided
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true },
where: {
extraData: {
some: {
AND: [
{ key: 'verse', value: verse },
{ key: 'surah', value: surah }
]
}
}
}
}
}
});
} else if (surah) {
// Condition 2: Only surah is provided
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true },
where: {
extraData: {
some: { key: 'surah', value: surah }
}
}
}
}
});
} else if (verse) {
// Condition 3: Only verse is provided
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true },
where: {
extraData: {
some: { key: 'verse', value: verse }
}
}
}
}
});
}
let result;
if (verse && surah) {
// Condition 1: Both verse and surah are provided
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true },
where: {
extraData: {
some: {
AND: [
{ key: 'verse', value: verse },
{ key: 'surah', value: surah }
]
}
}
}
}
}
});
} else if (surah) {
// Condition 2: Only surah is provided
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true },
where: {
extraData: {
some: { key: 'surah', value: surah }
}
}
}
}
});
} else if (verse) {
// Condition 3: Only verse is provided
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true },
where: {
extraData: {
some: { key: 'verse', value: verse }
}
}
}
}
});
}
else {
// No filters provided, fetch all posts
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true }
}
}
});
}
else {
// No filters provided, fetch all posts
result = await prisma.collection.findFirst({
where: { id: collectionId },
include: {
posts: {
take: consts.ITEMS_PER_PAGE,
skip: (currentPage - 1) * consts.ITEMS_PER_PAGE,
include: { collection: true, extraData: true }
}
}
});
}
it didn't work
Which conditions didn't work as expected?
Verse and surah together
Surah or verse alone works fine
FOR SIMPLIFIYNG
The code below works, when commenting either part of
AND, but when putting them together doesn't work
const result = await prisma.post.findMany({
where: {
extension: 'SURAH_WITH_VERSE',
extraData: {
some: {
AND: [
{
key: 'surah',
value: '2',
},
// {
// key: 'verse',
// value: '283',
// },
],
},
},
},
select: {
title: true,
extraData: true,
},
});
const result = await prisma.post.findMany({
where: {
extension: 'SURAH_WITH_VERSE',
extraData: {
some: {
AND: [
{
key: 'surah',
value: '2',
},
// {
// key: 'verse',
// value: '283',
// },
],
},
},
},
select: {
title: true,
extraData: true,
},
});
Hmm, do you get the same issue if you use
every?