order by specify ids

[mkupriichuk]

Hi

I wanna order records by some ids. For do this i run the next query:

SELECT
    id, brand
FROM
    myTable
ORDER BY
    CASE WHEN id IN(5, 4) THEN
        1
    ELSE
        2
    END,
    brand;

Unfortunately, when I try to build this query using ts-sql-query orderByFromString, I get an error:

Caused By: Error: The column "case" is not part of the select clause

Its work if i use fragmentWithType

.orderBy(connection.fragmentWithType('string', 'required').sql`CASE WHEN id IN(5, 4) THEN
        1
    ELSE
        2
    END`)

but in this case I can't use dynamic strings

.orderBy(connection.fragmentWithType('string', 'required').sql`CASE WHEN id IN(${ids.join(', ')}) THEN
        1
    ELSE
        2
    END`)

error: Caused By: TypeError: value.__toSqlForCondition is not a function

ts-sql-query version 1.56.0

Thanks

[juanluispaz]

Hi,

That will not even compile. SQL templates receive values controlled in the database, like a column, expression, etc., not raw SQL, which makes the query safe from SQL injections. In that case, you will need to create a constant value that will be sent to the database as query parameters.

Your code will be like:

.orderBy(
  connection.fragmentWithType('string', 'required')
  .sql`CASE WHEN id IN(${connection.const(ids, 'custom', 'stringArray')}) THEN
        1
    ELSE
        2
    END
  `
)

Just to let you know, the array will be expanded correctly in the query according to the database rules.

I recommend you take a look at the custom reusables fragment page, which may be interesting to you.

Let me know if that works for you.

[mkupriichuk]

thanks, i use connection.const(ids.join(', '), 'string') and its work