Based on this schema:
```model Seed {
id ...
# orm-helpb
Bård
08/17/2021, 10:09 AMBased on this schema:
Copy code
model Seed {
id String @id @default(cuid())
uid String
score Int?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [uid], references: [id])
tasks Tasks[]
}r
Ryan
08/18/2021, 7:32 AM@Bård 👋
Something like this you mean?
Copy code
await prisma.seed.findMany({
where: { score: { gte: 400 } },
include: { user: true },
orderBy: { score: 'desc' },
})b
Bård
08/19/2021, 1:50 PMHey @Ryan, thanks for the reply.
Bård
08/19/2021, 1:51 PMI wrote the statement I needed in raw SQL:
Copy code
const seeds = await prisma.$queryRaw`
SELECT s1.*, count(s2.id)+1 as rank FROM "Seed" s1
JOIN "Seed" s2 on s2.score > s1.score or (s2.score = s1.score AND s2."updatedAt" < s1."updatedAt")
WHERE s1.id = ${id} GROUP BY s1.id
`;Bård
08/20/2021, 6:28 AMThis query gives me the rank of the game based on the score and filtered by the game id (id field in the query). Thing is I need to also return the result of the preceding rank and the ensuing row.
Lets say the game id I'm sending is rank 99 in the highscore list. What i want is rank 98 and rank 100 as well as my current game rank 99.
Sorry if this is moving more into general SQL-theory and maybe not that much Prisma, I will understand if I have to seek help else where 🙂
Cheers!
r
Ryan
08/20/2021, 6:31 AMThe query that you posted above would have to be run via
prisma.$queryRaw