how to use dynamic sorting and order?

[kiranimeid]

i have something like this:
sqlselect * from ticket where account = ${acc.id} order by ${orderby} ${order};

but when i run it, i got an error says:
'PostgresError: syntax error at or near "$2"'

I've searching on google/this repo, but i have no clue how i can achieve it. thank you.

[porsager]

I searched for "dynamic order by" on this repo, and this was the first issue in the results : #174

I think you should find your answer there ;)

[kiranimeid]

I must be using wrong keyword then. Thank you.

[porsager]

No, the issue is that your orderby column is sent as a parameter. Here is your sample query which should work (notice orderby is inside sql()):

const orderby = 'some_column'
const order = 'desc'

await sql`
  select 
    * 
  from ticket 
  where account = ${ acc.id }  
  order by ${ sql(orderby) } ${ order === 'desc' ? sql`desc` : sql`asc` }
`

Of course you could make the order by clause more dynamic, but that's almost always up to the specific userland implementation requirements. Imagine something like this:

const id = 1
const order = {
  username: 'asc'
  created_at: 'desc'
}

await sql`
  select 
    * 
  from ticket 
  where account = ${ id }  
  order by ${
    Object.entries(order).flatMap(([column, order], i) =>
      [i ? sql`,` : sql``, sql`${ sql(column) } ${ order === 'desc' ? sql`desc` : sql`asc` }`]
    )
  }
`

The whole point of all this is to ensure we only have statically defined strings that compose the query. Thereby we prevent any kind of sql injection.