Sorting Lists

Now let’s update our GET /v1/movies endpoint so that the client can specify how the movies are sorted in the JSON response.

As we briefly explained earlier, we want to let the client control the sort order via a query string parameter in the format sort={-}{field_name}, where an optional - prefix is used to indicate a descending sort order. For example:

// Sort the movies on the title field in ascending alphabetical order.
/v1/movies?sort=title

// Sort the movies on the year field in descending numerical order.
/v1/movies?sort=-year

Behind the scenes, we will want to translate this into an ORDER BY clause in our SQL query, so that a query string parameter like sort=-year would result in a SQL query like this:

SELECT id, created_at, title, year, runtime, genres, version
FROM movies
WHERE (STRPOS(LOWER(title), LOWER($1)) > 0 OR $1 = '') 
AND (genres @> $2 OR $2 = '{}')     
ORDER BY year DESC --<-- Order the result by descending year

The difficulty here is that the values for the ORDER BY clause need to be generated at runtime based on the query string values from the client. Ideally we’d use placeholder parameters to insert these dynamic values into our query, but unfortunately it’s not possible to use placeholder parameters for column names or SQL keywords (including ASC and DESC).

So instead, we’ll need to interpolate these dynamic values into our query using fmt.Sprintf() — making sure that the values are checked against a strict safelist first to prevent a SQL injection attack.

When working with PostgreSQL, it’s also important to be aware that the order of returned rows is only guaranteed by the rules that your ORDER BY clause imposes. From the official documentation:

If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

That means if we don’t include an ORDER BY clause, then PostgreSQL may return the movies in any order — and the order may or may not change each time the query is run.

Likewise, in our database multiple movies will have the same year value. If we order based on the year column, then the movies are guaranteed to be ordered by year, but the movies for a particular year could appear in any order at any time.

This point is particularly important in the context of an endpoint which provides pagination. We need to make sure that the order of movies is perfectly consistent between requests to prevent items in the list ‘jumping’ between the pages.

Fortunately, guaranteeing the order is simple — we just need to ensure that the ORDER BY clause always includes a primary key column (or another column with a unique constraint on it). So, in our case, we can apply a secondary sort on the id column to ensure an always-consistent order. Like so:

SELECT id, created_at, title, year, runtime, genres, version
FROM movies
WHERE (STRPOS(LOWER(title), LOWER($1)) > 0 OR $1 = '') 
AND (genres @> $2 OR $2 = '{}')     
ORDER BY year DESC, id ASC

Implementing sorting

To get the dynamic sorting working, let’s begin by updating our Filters struct to include some sortColumn() and sortDirection() helpers that transform a query string value (like -year) into values we can use in our SQL query.

package data

import (
    "slices"  // New import
    "strings" // New import

    "greenlight.alexedwards.net/internal/validator"
)

type Filters struct {
    Page         int
    PageSize     int
    Sort         string
    SortSafelist []string
}

// Check that the client-provided Sort field matches one of the entries in our safelist
// and if it does, extract the column name from the Sort field by stripping the leading
// hyphen character (if one exists).
func (f Filters) sortColumn() string {
    if slices.Contains(f.SortSafelist, f.Sort) {
        return strings.TrimPrefix(f.Sort, "-")
    }

    panic("unsafe sort parameter: " + f.Sort)
}

// Return the sort direction ("ASC" or "DESC") depending on the prefix character of the
// Sort field.
func (f Filters) sortDirection() string {
    if strings.HasPrefix(f.Sort, "-") {
        return "DESC"
    }

    return "ASC"
}

...

Notice that the sortColumn() function is constructed in such a way that it will panic if the client-provided Sort value doesn’t match one of the entries in our safelist. In theory this shouldn’t happen — the Sort value should have already been checked by calling the ValidateFilters() function — but this is a sensible failsafe to help stop a SQL injection attack from occurring.

Now let’s update our internal/data/movies.go file to call those methods, and interpolate the return values into our SQL query’s ORDER BY clause. Like so:

package data

import (
    "context"
    "database/sql"
    "errors"
    "fmt" // New import
    "time"

    "greenlight.alexedwards.net/internal/validator"

    "github.com/lib/pq"
)

...

func (m MovieModel) GetAll(title string, genres []string, filters Filters) ([]*Movie, error) {
    // Add an ORDER BY clause and interpolate the sort column and direction. Importantly
    // notice that we also include a secondary sort on the movie ID to ensure a
    // consistent ordering.
    query := fmt.Sprintf(`
        SELECT id, created_at, title, year, runtime, genres, version
        FROM movies
        WHERE (to_tsvector('simple', title) @@ plainto_tsquery('simple', $1) OR $1 = '') 
        AND (genres @> $2 OR $2 = '{}')     
        ORDER BY %s %s, id ASC`, filters.sortColumn(), filters.sortDirection())

    // Nothing else below needs to change.
    ...
}

And once that’s done, we should be ready to try this out.

Restart the application then, as an example, go ahead and try making a request for the movies sorted by descending title. You should get a response which looks like this:

$ curl "localhost:4000/v1/movies?sort=-title"
{
    "movies": [
        {
            "id": 4,
            "title": "The Breakfast Club",
            "year": 1985,
            "runtime": "97 mins",
            "genres": [
                "comedy"
            ],
            "version": 5
        },
        {
            "id": 1,
            "title": "Moana",
            "year": 2016,
            "runtime": "107 mins",
            "genres": [
                "animation",
                "adventure"
            ],
            "version": 1
        },
        {
            "id": 2,
            "title": "Black Panther",
            "year": 2018,
            "runtime": "134 mins",
            "genres": [
                "sci-fi",
                "action",
                "adventure"
            ],
            "version": 2
        }
    ]
}

In contrast, using a sort parameter of descending runtime should yield a response in a totally different order. Similar to this:

$ curl "localhost:4000/v1/movies?sort=-runtime"
{
    "movies": [
        {
            "id": 2,
            "title": "Black Panther",
            "year": 2018,
            "runtime": "134 mins",
            "genres": [
                "sci-fi",
                "action",
                "adventure"
            ],
            "version": 2
        },
        {
            "id": 1,
            "title": "Moana",
            "year": 2016,
            "runtime": "107 mins",
            "genres": [
                "animation",
                "adventure"
            ],
            "version": 1
        },
        {
            "id": 4,
            "title": "The Breakfast Club",
            "year": 1985,
            "runtime": "97 mins",
            "genres": [
                "comedy"
            ],
            "version": 5
        }
    ]
}