Bind query Order By with params?

[grippy]

Maybe this exists and I couldn't find anything in the docs: Is it possible to control the sorting on a :many query using the input params?

When I run the following query, I don't see the ability to pass OrderBy in the getStuffParams:

const getStuff = `-- name: GetStuff :many
SELECT *
FROM stuff
ORDER BY $3
OFFSET $1 LIMIT $2
`
type getStuffParams struct {
	Offset int32 `json:"offset"`
	Limit  int32 `json:"limit"`
}

Since the order by value is a $variable it we be nice if getStruffParams would generate something like this, instead:

type OrderBy struct {
     col string
     order string
}

type getStuffParams struct {
	Offset int32 `json:"offset"`
	Limit  int32 `json:"limit"`
        OrderBy []OrderBy `json:"order_by"`
}

In the case where order by is hard-coded in the query, then there wouldn't be a need to add "OrderBy" to the params.
It should be possible to mix both: order by x desc, $1 or order by $1, x desc.

This pattern would also enable dynamically generating HAVING and GROUP BY clauses.

[aitva]

What you want is a bit tricky, ORDER BY expects an SQL expression and not a list of text parameters. We can already pass 1 text parameter but it is ignored by the database, see #982. The easiest way I found to sort the results of a query based on its parameters is to use a CASE expression:

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: ListAuthors :many
SELECT *
FROM authors
ORDER BY CASE
    WHEN @order_by::text = 'name' THEN name
    WHEN @order_by::text = 'id' THEN id
END;

We can go further and change the sort order dynamically but it gets ugly:

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: ListAuthors :many
SELECT *
FROM authors
ORDER BY CASE
    WHEN NOT @reverse::boolean AND @order_by::text = 'name' THEN name
    WHEN NOT @reverse::boolean AND @order_by::text = 'id' THEN id
END ASC, CASE
    WHEN @reverse::boolean AND @order_by::text = 'name' THEN name
    WHEN @reverse::boolean AND @order_by::text = 'id' THEN id
END  DESC;

Depending on the value of @reverse, one part of the ORDER BY will resolve to NULL while the other will return a field. This is required because ASC and DESC are SQL keyword and cannot be injected in the query.

See https://www.postgresql.org/docs/11/sql-select.html

[piavgh]

@aitva : Using this solution, I got error ERROR: CASE types text and timestamp with time zone cannot be matched (SQLSTATE 42804) or ERROR: CASE types text and integer cannot be matched (SQLSTATE 42804) because my columns are not the same type

What you want is a bit tricky, ORDER BY expects an SQL expression and not a list of text parameters. We can already pass 1 text parameter but it is ignored by the database, see #982. The easiest way I found to sort the results of a query based on its parameters is to use a CASE expression:

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: ListAuthors :many
SELECT *
FROM authors
ORDER BY CASE
    WHEN @order_by::text = 'name' THEN name
    WHEN @order_by::text = 'id' THEN id
END;

We can go further and change the sort order dynamically but it gets ugly:

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: ListAuthors :many
SELECT *
FROM authors
ORDER BY CASE
    WHEN NOT @reverse::boolean AND @order_by::text = 'name' THEN name
    WHEN NOT @reverse::boolean AND @order_by::text = 'id' THEN id
END ASC, CASE
    WHEN @reverse::boolean AND @order_by::text = 'name' THEN name
    WHEN @reverse::boolean AND @order_by::text = 'id' THEN id
END  DESC;

Depending on the value of @reverse, one part of the ORDER BY will resolve to NULL while the other will return a field. This is required because ASC and DESC are SQL keyword and cannot be injected in the query.

See https://www.postgresql.org/docs/11/sql-select.html

[tardisx]

@aitva : Using this solution, I got error ERROR: CASE types text and timestamp with time zone cannot be matched (SQLSTATE 42804) or ERROR: CASE types text and integer cannot be matched (SQLSTATE 42804) because my columns are not the same type

You can add more CASE statement pairs, one for each type.