CODEWITHSUNDEEP
javascriptsqlsqliteknex.js
Luke Celitan
Luke Celitan

Reputation: 340

How to join a count of foreign table entries in knex.js?

I am building a sort query using knex.js. I would like to join a count of foreign table entries and sort the results by this joined table count.

So far I have come up with this code: 

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;
}

This query shows results but they are not sorted by 'likes'. Furthermore changing the sorter value from true to false does not change the order.

Upvotes: 0

Views: 766

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

You are not quoting as Count correctly so order by "Count" will not match any column.

This might help:

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

Upvotes: 1

Related Questions