如何在Knex.js中按计算列进行排序？

这个问题我之前也碰到过，其实核心原因是：你遇到的错误来自于一个自动生成的count统计查询（大概率是分页逻辑触发的），这个查询里根本没包含你定义的age计算列，但排序语句却引用了这个别名，数据库自然找不到它。另外就算是主查询，有些数据库也不支持在ORDER BY里直接用SELECT子句的别名（虽然MySQL大部分情况支持，但Knex的自动查询生成可能打破了这个逻辑）。给你几个靠谱的解决办法：

方案一：在排序时重复计算表达式 #

最简单直接的方式，就是把orderBy里的别名换成完整的计算逻辑，这样不管是主查询还是自动生成的count查询，都能识别排序条件：

knex.select(knex.raw('DATEDIFF("2018-04-15", STR_TO_DATE(birthdate, "%d/%m/%Y")) / 365.25 as age'))
.from('user')
.orderBy(knex.raw('DATEDIFF("2018-04-15", STR_TO_DATE(birthdate, "%d/%m/%Y")) / 365.25'), 'desc');

虽然看起来有点冗余，但胜在简单直接，不需要改太多代码。

方案二：用子查询/CTE预先计算虚拟列 #

如果觉得重复写计算逻辑不够优雅，可以用子查询先把包含age的结果集查出来，再在外层排序：

knex.select('*')
.from(function() {
  // 内层子查询计算出age列，并命名为临时表
  this.select(knex.raw('DATEDIFF("2018-04-15", STR_TO_DATE(birthdate, "%d/%m/%Y")) / 365.25 as age'))
    .from('user')
    .as('user_with_age');
})
.orderBy('age', 'desc');

这样外层查询可以直接用age别名排序，因为临时表已经包含了这个计算好的列，同时自动生成的count查询也会基于这个临时表，不会再出现找不到列的问题。

额外说明 #

你看到的错误查询select count(user.id) as totalfromuserorder byiddesc,age desc，应该是Knex的分页插件（比如knex-paginate）自动生成的统计语句。这类插件默认只会复制主查询的排序条件，但不会复制SELECT子句里的计算列，所以就出现了“排序引用不存在的列”的问题。上面两个方案都能完美解决这个场景。

内容的提问来源于stack exchange，提问作者ryd3r