0

I am fetching an issue where I have to generate dynamic ORDER BY clause for multiple columns.

Here is my sample query:

SELECT *
FROM user
WHERE gender = 'male'
  ORDER BY created_at ASC, updated_at DESC
LIMIT 10 OFFSET 0;

Here, ORDER BY values will be dynamic and it is coming from user end.

My Progress:

const orderBy: string = 'ORDER BY created_at ASC, updated_at DESC';

SELECT *
FROM user
WHERE gender = 'male'
  ${orderBy}
LIMIT 10 OFFSET 0;

Here, if I pass the ORDER BY clause as ${orderBy}, then it takes it as a value. It shows an error.

The SLONIK-generated SQL is like the below:

{
  sql: "\n  SELECT *\nFROM user\nWHERE gender = 'male'\n  $1\nLIMIT 10 OFFSET 0;\n",
  type: 'SLONIK_TOKEN_SQL',
  values: [ 'ORDER BY created_at ASC, updated_at DESC' ]
}

Thanks in advance.

Md. Sajedul Karim
  • 6,749
  • 3
  • 61
  • 87
  • How are you getting user input? You created the string variable `orderBy` and directly used it in. Before running the query, you'll have to edit the user values onto the orderBy string. – Jishan Shaikh Dec 12 '22 at 13:51

1 Answers1

0

I was having the same problem. According to their docs, you need to create another query:

const query0 = sql.unsafe`SELECT ${'foo'} FROM bar`;
const query1 = sql.unsafe`SELECT ${'baz'} FROM (${query0})`;

Check their documentation: Nesting sql

Beto
  • 1
  • 1
  • 1