Dynamic ORDER BY

[herenickname]

const orderBy = 'random()' // OR 'volume DESC'

const rows = await sql<ItemEntity[]>/*sql*/ `
  SELECT *
  FROM items
  ORDER BY ${sql(orderType)}
`

The generated query has ORDER BY $1 in it, but it feels like the $1 value is not being sent to the database. All tested results are sorted the same way.

[porsager]

You need sql fragments for literal sql.

Do const orderBy = sql`random()`

[herenickname]

You need sql fragments for literal sql.

Do const orderBy = sql`random()`

const type = 1
const _order = 'ASC'
const order = sql`id ${_order}`

const [row] = await sql<ItemEntity[]>`
  SELECT *
  FROM items
  WHERE type = ${type}
  ORDER BY ${order}
  LIMIT 1
`

error: Uncaught (in promise) PostgresError: syntax error at or near "$2" const error = Errors.postgres(parseError(x)) at ErrorResponse (https://deno.land/x/postgresjs@v3.3.5/src/connection.js:775:26)

---

How do I create multi-level "order by" with postgres.js? Example: const orders = [{id: 'DESC', name: 'ASC'}] How do I pack this into a query?

[porsager]

It's been answered before 😉 Search the issues. "dynamic order by" should give you something.

[herenickname]

It's been answered before 😉 Search the issues. "dynamic order by" should give you something.

Thanks for your reply and your work, but I assume such basic questions with examples should be in the readme/wiki of the project. A response like searching through a large number of issues is depressing.

[herenickname]

Personally, I don't fully understand the logic behind how sql literals work (when it's better to call with brackets and when it's better to call with a tilde). It would be cool to have more examples with peculiarities. The library features are not clear to the end.

[porsager]

That's the great thing about open source ;) And PRs to doc improvements are welcome..

To be honest - your approach is what is depressing - the correct open source approach from users ought to be search first, instead of ask first, and then a PR with improvements. The users are the ones getting something potentially beneficial for free, so you would think that it's the creator / maintainer that is entitled to receive help, instead of the user being entitled to it.

[herenickname]

That's the great thing about open source ;) And PRs to doc improvements are welcome..

To be honest - your approach is what is depressing - the correct open source approach from users ought to be search first, instead of ask first, and then a PR with improvements. The users are the ones getting something potentially beneficial for free, so you would think that it's the creator / maintainer that is entitled to receive help, instead of the user being entitled to it.

Your point is clear and fair.

If someone else encounters this problem, the solution:

const orderBy = sql`random()` 
/* OR */
const orderBy = sql`volume DESC`

const rows = await sql<ItemEntity[]>`
  SELECT *
  FROM items
  ORDER BY ${orderBy}
`