如何基于下拉框选择动态修改Flask-SQLAlchemy的order_by排序规则？

1. 定义排序白名单映射&修改查询函数 #

首先需要先做合法排序字段的白名单映射，避免用户传入非法参数引发SQL注入风险，同时给查询函数增加可传入的排序参数，设置默认值保证页面初始加载正常。
修改后的代码如下：

# 先定义排序规则白名单映射，key对应前端下拉框传的参数值，value对应实际排序表达式
SORT_OPTIONS = {
    "username_asc": User.username.asc(),
    "username_desc": User.username.desc(),
    "followers_asc": "total_followers asc",
    "followers_desc": "total_followers desc"
}
# 默认排序规则，可根据需求自行调整
DEFAULT_SORT = "followers_desc"

def get_curators_and_total_followers(sort_by=DEFAULT_SORT):
    total_playlist_followers =  db.session.query(SpotifyPlaylist.curator_id.label('user_id'), db.func.sum(SpotifyPlaylist.playlist_follower_count).label("total_followers"))\
        .outerjoin(User, SpotifyPlaylist.curator_id==User.id)\
        .group_by(User.id)\
        .subquery()
    # 校验传入的排序参数是否合法，不合法则用默认值
    sort_expr = SORT_OPTIONS.get(sort_by, SORT_OPTIONS[DEFAULT_SORT])
    curators = db.session.query(User, total_playlist_followers.c.total_followers)\
        .outerjoin(total_playlist_followers, User.id==total_playlist_followers.c.user_id)\
        .join(SpotifyPlaylist, SpotifyPlaylist.curator_id==User.id)\
        .order_by(sort_expr)   
    return curators

2. 路由层适配 #

在对应的页面路由中，接收前端下拉框传递的排序参数，将参数传入查询函数即可，示例如下：

@app.route('/curators')
def curators_list():
    # 从GET请求的查询参数中获取排序值，没有传就用默认值
    sort_by = request.args.get("order_by", DEFAULT_SORT)
    curators = get_curators_and_total_followers(sort_by)
    # 后续返回渲染页面或者接口数据的逻辑

3. 前端下拉框配置 #

前端下拉框的option的value对应SORT_OPTIONS里的key即可，示例：

<select id="sort-select" onchange="window.location.search=`?order_by=${this.value}`">
  <option value="followers_desc" selected>粉丝数从高到低</option>
  <option value="followers_asc">粉丝数从低到高</option>
  <option value="username_asc">用户名升序</option>
  <option value="username_desc">用户名降序</option>
</select>

所有用户可传入的排序规则必须提前在SORT_OPTIONS里定义，禁止直接将用户输入的原始内容传入order_by方法，避免SQL注入风险。

内容的提问来源于stack exchange，提问作者Adventure-Knorrig