Problem concatenating strings using JavaScript template literals-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.