hello, I have a raw query (postgres) here with an ...
# orm-helpd
Dev__
07/05/2021, 12:51 PMhello, I have a raw query (postgres) here with an
ORDER BY Copy code
this.prisma.$queryRaw`
SELECT *
FROM "public"."OrderStatus"
INNER JOIN "public"."OrderStatusDescription" ON ("public"."OrderStatus"."id" = "public"."OrderStatusDescription"."orderStatusId")
WHERE "public"."OrderStatusDescription"."languageId" = ${languageId}
ORDER BY "public"."OrderStatusDescription"."name" DESC LIMIT ${pagination.take} OFFSET ${pagination.skip}
`DESC Copy code
ORDER BY "public"."OrderStatusDescription"."name" ${order.toUpperCase()} // order = 'asc' | 'desc'Dev__
07/05/2021, 1:00 PMsame thing happens with parameterized queries
d
Dominic Hadfield
07/05/2021, 1:01 PMcould you log out the string that is created for the query? eg
SELECT …Dominic Hadfield
07/05/2021, 1:01 PMit might not be removing the speech marks causing
'ASC'ASCd
Dev__
07/05/2021, 1:04 PMDev__
07/05/2021, 1:04 PMthere's no extra quotes around it
d
Dominic Hadfield
07/05/2021, 1:06 PMlanguageid looks like it is in the wrong format
Dominic Hadfield
07/05/2021, 1:07 PMits not being interpreted as a string
Dominic Hadfield
07/05/2021, 1:07 PM Copy code
WHERE "public"."OrderStatusDescription"."languageId" = "${languageId}"d
Dev__
07/05/2021, 1:09 PMdont think that matters much. though it still doesnt work
Dev__
07/05/2021, 1:09 PMsame error on the same place
d
Dominic Hadfield
07/05/2021, 1:14 PMhmmm, I’m not sure how I can help you. @Ryan could help maybe
Dominic Hadfield
07/05/2021, 1:14 PMhave you tried without converting to uppercase?
d
Dev__
07/05/2021, 1:21 PMno 😞
Dev__
07/05/2021, 1:21 PMi need nested sort on
OrderStatusDescriptionr
Ryan
07/05/2021, 1:29 PMYou would need to do something like this for adding
ascdesc Copy code
await prisma.$queryRaw`select * from table order by field ${
order === 'desc' ? Prisma.sql`desc` : Prisma.sql`asc`
}`d
Dev__
07/05/2021, 1:36 PMyes thanks, that works! by why didnt this work
Copy code
${Prisma.sql`${order}`}Dev__
07/05/2021, 1:38 PMalso, I am trying to selected the field dynamically
Copy code
ORDER BY "public"."OrderStatusDescription"."name" Copy code
ORDER BY "public"."OrderStatusDescription"."${orderBy}"column doesnt existsDev__
07/05/2021, 1:38 PM@Ryan
r
Ryan
07/05/2021, 1:51 PMyes thanks, that works! by why didnt this workRaw query support is based on this library and whatever you interpolate inside is taken as a value, which is why you need to do this.
also, I am trying to selected the field dynamicallyThat’s tricky. I would need to check for this.
Ryan
07/05/2021, 1:53 PMThis would work, but you need to sanitise the field first:
Copy code
let order = 'desc'
let field = 'id'
await prisma.$queryRaw`select * from User order by ${Prisma.raw(field)} ${
order === 'desc' ? Prisma.sql`desc` : Prisma.sql`asc`
}`d
Dev__
07/05/2021, 1:57 PMthanks! it works fine like this
${Prisma.raw(orderBy)}👍 1
8 Views