Support dynamic order by clause

go-aegian commented 1 year ago

the order by clause in the :many definition query should be set dynamically

-- name: ListOrders :many
  select * from order where status = $1 order by @orderBy::text;

Generated

const listOrders = "select * from order where status = $1 order by @orderBy::text"

type ListOrdersParams struct {
       Status string
       OrderBy string
}

func (q *Queries) ListOrders(ctx context.Context, arg ListOrdersParams) ([]*ListOrdersRow, error) {
        orderBy:=arg.OrderBy
        if orderBy=="" {
           orderBy= "0"
        }
        listOrdersWithOrderBy = strings.Replace(listOrders, "@orderBy::text", orderBy, -1)

    rows, err := q.db.Query(ctx, listOrdersWithOrderBy, arg.Status)

...

What database engines need to be changed?

PostgreSQL, MySQL

What programming language backends need to be changed?

Go, Python, Kotlin

jwc-clinnection commented 1 year ago

I have implemented this in #2343

seanlaff commented 10 months ago

I understand the desire to prevent sqlc turning into a templating language, as that defeats the "it's just sql" pitch. Perhaps there's a middle ground using existing sqlc patterns to cover the most common ORDER BY use case.

I'm imagining a new macro sqlc.orderBy() that takes inspiration from sqlc.slice(). Sqlc could use the AST to figure out what columns are available to sort and present those as typesafe args in the generated code.

SELECT id, name FROM authors ORDER BY sqlc.orderBy(myOrderBy);

-- use it as many times as you want
SELECT name
FROM (
  SELECT id, name FROM authors ORDER BY sqlc.orderBy(myInnerOrderBy) LIMIT 10
)
ORDER BY sqlc.orderBy(myOuterOrderBy)

Generating code like

type ListAuthorsOrderByFoo string

const (
    ListAuthorsSortIdAsc     ListAuthorsOrderByFoo = "id_asc"
    ListAuthorsSortIdDesc    ListAuthorsOrderByFoo = "id_desc"
    ListAuthorsSortNameAsc   ListAuthorsOrderByFoo = "name_asc"
    ListAuthorsSortNameDesc  ListAuthorsOrderByFoo = "name_desc"
)

const listAuthors = `-- name: ListAuthors :many
SELECT id, name FROM authors
ORDER BY /*ORDERBY:foo*/` // <- similiar to how sqlc handles slices for mysql

func (q *Queries) ListAuthors(ctx context.Context, foo []ListAuthorsFooOrderBy) ([]Author, error) {
  // ... map orderBy args to the query
}

A solution like that might move the needle enough to solve a lot of the order-by blockers. It doesn't support dynamic expressions, but at least it handles the most common use case of wanting to control the sort order of the columns present in your projection.

danielbraun89 commented 6 months ago

Any update on this?

marcomayer commented 6 months ago

I stumbled into this too, as it's quite common to have some kind of sorting that needs to be flexible. Think of showing data to users that they can sort by a number of different columns.

Is there a workaround for this besides having multiple queries defined with just the order by being different? Maybe with COALESCE or so? 🤔

shagohead commented 4 months ago

At now for having configurable ORDER BY clause you can do it like this:

ORDER BY
CASE WHEN @order_by::varchar = 'id_asc' THEN authors.id END ASC,
CASE WHEN @order_by = 'id_desc' THEN authors.id END DESC,
CASE WHEN @order_by = 'birth_year_asc' THEN authors.birth_year END ASC,
CASE WHEN @order_by = 'birth_year_desc' THEN authors.birth_year END DESC

But it will be a neat to have some enumerated type instead of just a string.