postgresql统计排序查询（排序值中含有null排序和关联查询排序）

let order = [[ 'id', 'DESC' ]];
let order_count_sql = sequelize.literal('(select cast(COUNT(*) as integer) from orders as orders where ("orders"."deletedAt" IS NULL) AND orders.teacher_id="teacher"."id" AND state=40)');
        let order_count = [ order_count_sql, 'order_count' ];
        let sales_sql = sequelize.literal('(SELECT sum("price") AS "sum" FROM "orders" AS "orders" where ("orders"."deletedAt" IS NULL) AND orders.teacher_id="teacher"."id" AND state=40)');
        let sales = [ sales_sql, 'sales' ];
        let score = [ sequelize.literal(`(select AVG("score") AS "score" from evaluates as evaluates where evaluates.teacher_id="teacher"."id" AND type='${'order'}')`), 'score' ];

// overlap
let goods_count = [ sequelize.literal('(SELECT cast(COUNT(*) as integer) AS "count" FROM "goods" AS "goods" WHERE "goods"."deletedAt" IS NULL AND "goods"."state" = 1 AND "goods"."classes" && ARRAY["class"."id"]::INTEGER[])'), 'goods_count' ];

        if (order_count_order) {
            if (order_count_order === 'ASC')order.unshift([ order_count_sql, 'ASC nulls first' ]);
            if (order_count_order === 'DESC')order.unshift([ order_count_sql, 'DESC nulls last' ]);
        }
        if (sales_order) {
            if (sales_order === 'ASC')order.unshift([ sales_sql, 'ASC nulls first' ]);
            if (sales_order === 'DESC')order.unshift([ sales_sql, 'DESC nulls last' ]);
        }

        let { rows, count } = await TeacherModel.findAndCountAll({
            'attributes': [ 'id', 'hash', 'openid', 'avatar', 'name', 'nickname', 'sex', 'phone', 'subject_id', 'teacher_age', 'state', 'money',
                'wx', 'createdAt', order_count, sales, score ],
            'where': {
                ...(name && { 'name': { '$like': `%${name}%` } }),
                ...(nickname && { 'nickname': { '$like': `%${nickname}%` } }),
                ...(phone && { phone }),
                ...(state !== 'all' && { state }),
            },
            order,
            limit,
            offset,
        });

 // include 关联查询排序

 let { rows, count } = await GoodsModel.findAndCountAll({
            'attributes': { 'exclude': [ 'deletedAt', 'updatedAt' ] },
            limit,
            where,
            offset,
            'distinct': true, // 去重
            'include': [{
                'attributes': { 'exclude': [ 'deletedAt', 'updatedAt' ] },
                'model': SkuModel,
                'as': 'skus',
            }],
            'order': [[ 'createdAt', 'DESC' ], [{ 'model': SkuModel, 'as': 'skus' }, 'price', 'ASC' ]],
        });

//  [{ 'model': SkuModel, 'as': 'skus' }, 'price', 'ASC' ]] 还可以写成 ['skus', 'price', 'ASC']