【Go Web开发】查询列表排序
上一篇文章我们介绍了根据关键字搜索返回电影列表，现在让我们更新GET /v1/movies接口的逻辑，以便客户端可以控制如何在JSON响应中对电影列表进行排序。
如前所述，我们希望让客户端通过发送sort={-}{field_name}格式的查询字符串参数来控制查询列表的顺序，其中可选的"-"字符用于指示降序排序。例如:
// 以title字段对movies进行按字母顺序排序
/v1/movies?sort=title
// 以year字段对movies进行按数字大小降序排序
/v1/movies?sort=-year
在后台，我们希望在SQL查询中将其转换为ORDER BY子句，这样sort=-year之类的查询字符串参数就会生效:
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 --<-- 将查询结果按照year降序排列
这里的困难在于，ORDER BY子句的值需要在运行时根据客户端发送的查询字符串值动态生成。理想情况下，我们应该使用占位符参数将这些动态值插入查询中，但不幸的是，不能将占位符参数用于表中的列名或SQL关键字(包括ASC和DESC)。
因此，我们需要使用fmt.Sprintf()将这些动态值插入到查询中——首先根据safelist检查这些值，以防止SQL注入攻击。当使用PostgreSQL时，同样重要的是要意识到，返回的row的顺序只能由你的ORDER BY子句所规定的规则来保证。根据官方文档:
如果没有使用排序，查询结果将以随机顺序返回。这种情况下的实际顺序将取决于表扫描和连接计划类型以及磁盘上的顺序，但不能依赖它。要确保特定顺序的返回，必须选择排序方式。
这意味着如果我们不包含ORDER BY子句，那么PostgreSQL数据库可以以任何顺序返回电影记录——并且每次运行查询时，顺序可能改变，也可能不改变。
同样地，在我们的数据库中，多个movies将具有相同的年份值。如果我们基于年份排序，那么电影就保证按年份排序，但是相同年份的电影可以在任何时间以任何顺序出现。这一点在提供分页的接口上下文中尤其重要。我们需要确保请求之间的电影顺序完全一致，以防止列表中的项目在页面之间“跳跃”。
幸运的是，保证顺序一致很简单—我们只需要确保ORDER BY子句始终包含一个主键列(或另一个对其有惟一约束的列)。因此，在我们的例子中，可以对id列应用二级排序，以确保始终一致的顺序。像这样:
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
实现排序
为了实现查询列表的排序，为Filters结构体添加sortColumn()和sortDirection()帮助函数，将url中的排序参数转换成SQL能用的值。
File：internal/data/filters.go
package data
...
// 检查客户端提供排序参数是否符合safelist中提供的字段，提取其中的字符串
func (f Filters)sortColumn() string {
for _, safeValue := range f.SortSafelist{
if f.Sort == safeValue {
return strings.TrimPrefix(f.Sort, "-")
}
}
panic("unsafe sort parameter: " + f.Sort)
}
// 提取排序字符串中的"-"符号，判断是否为降序排序
func (f Filters)sortDirection() string {
if strings.HasPrefix(f.Sort, "-") {
return "DESC"
}
return "ASC"
}
请注意，sortColumn()函数的构造方式是：如果客户端提供的Sort值与safelist中的条目不匹配，它就会panic。从理论上讲，这是不应该发生的——Sort值已经通过调用ValidateFilters()函数完成检查了——但这是一个合理的安全检查，可以阻止SQL注入攻击的发生。
现在更新internal/data/movies.go文件调用上面的方法，将返回的字符串应用到ORDER BY字句中：
File：internal/data/movies.go
package data
...
func (m MovieModel)GetAll(title string, genres []string, filters Filters) ([]*Movie, error) {
// 将排序字符串和排序方式插入到SQL查询中去
query := fmt.Sprintf(`
SELECT id, create_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())
//创建3s超时上下文实例
ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
defer cancel()
...
}
重启服务，然后使用title字段来尝试请求列表排序。你应该能得到如下响应结果：
$ curl "localhost:4000/v1/movies?sort=-title"
{
"movies": [
{
"id": 4,
"title": "The Breakfast Club",
"year": 1985,
"runtime": "97 mins",
"genres": [
"comedy",
"drama"
],
"Version": 15
},
{
"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
}
]
}
相反，使用runtime降序排序参数应该产生一个完全不同的响应结果。如下所示:
$ 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",
"drama"
],
"Version": 15
}
]
}
