javascript - 如何在 knex.js 中加入外部表条目的计数？

Question

我正在使用 knex.js 构建排序查询。我想加入外部表条目的计数，并按此加入的表计数对结果进行排序。

到目前为止，我已经想出了这段代码:

async eventsPaginationB2C(limit, after, filter, sort) {
    const queryBuilder = knex
      .select(
        'e.id as id',
        'e.title as title',
        'e.description as description',
        'e.startDate as startDate',
        'e.endDate as endDate',
        'e.userId as userId',
        'e.username as username',
        'e.status as status',
        'e.lang as lang',
        'e.online as online',
        'e.range as range',
        'e.projectName as projectName',
        'e.repeatingEvent as repeatingEvent',
        'e.ranking as ranking'
      )
      .limit(limit)
      .offset(after)
      .from('event as e')
      .where(function() {
        this.where('status', 'live');
      })
      .where(function() {
        this.where('range', 'global');
      });

if(sort){

if (hasIn(sort, 'likes')) {
        const desc = sort.likes.desc === true ? 'desc' : 'asc';
        queryBuilder
          .join(knex.raw(`(SELECT  *, COUNT(likes.user_id) AS Count FROM likes  WHERE id = likes.event_id )`))
          .orderBy('Count', desc);
      }

}

return queryBuilder;
}

此查询显示结果，但它们未按“喜欢”排序。此外，将排序器值从 true 更改为 false 不会更改顺序。

Answer 1

您没有正确引用作为Count，因此order by "Count"将不匹配任何列。

这可能有帮助:

knex.raw(`(SELECT  *, COUNT(likes.user_id) AS ?? FROM likes  WHERE id = likes.event_id )`, ['Count'])