I'm using the prisma.io library and having trouble working with template literals to do what I want. Maybe I don't understand them well enough.
I have this call:
const result = await prisma.$queryRaw`SELECT * FROM User WHERE active = ${activeSetting}`
And this works exactly the way I want.
However, I want to optionally append a string at the end without having to redo the full statement.
That is, I want something like:
let result;
if (orderBy === "ASC") {
const result = await prisma.$queryRaw`SELECT * FROM User WHERE active =
${activeSetting} ORDER BY ASC`
} else if {
const result = await prisma.$queryRaw`SELECT * FROM User WHERE active =
${activeSetting} ORDER BY DESC`
}
Basically, I don't want to have to have to complete statements that start with const result = await. I realize this is a short example, but my problem is when I have fairly long SQL statements and I find myself doing a lot of cutting and pasting huge SQL which gets error prone.
You can use a ternary operator like so:
const result = await prisma.$queryRaw`SELECT * FROM User WHERE active =
${activeSetting} ORDER BY ${orderBy === 'ASC' ? 'ASC' : 'DESC'}`
But it is better not to write raw SQL strings because you could leave yourself vulnerable to SQL Injection
I realize I could have made a much bigger sql statement as well as concatenating bigger chunks of code and that would have made the problem more clear. Thanks to @CertainPerformance and @Phil for the ideas.
Here is what I was really looking for that solves my bigger problem and still keeps me safe I'm pretty sure.
const orderByAsc = Prisma.sql`ORDER BY ASC`;
const orderByDesc = Prisma.sql`ORDER BY DESC`;
const result =
await prisma.$queryRaw`SELECT * FROM User WHERE active = ${activeSetting} ${
orderBy === 'ASC' ? orderByAsc : orderByDesc
}`;
And it also let's me put expressions in my sub clauses if need be.