how to get one page data list and total count from database with knex.js?

Question

I have a user table with some records(such as 100), how can I get one page data and total count from it when there are some where conditions?

I tried the following:

var model = knex.table('users').where('status',1).where('online', 1)
var totalCount = await model.count();
var data = model.offset(0).limit(10).select()
return {
    totalCount: totalCount[0]['count']
    data: data
}

but I get { "totalCount": "11", "data": [ { "count": "11" } ] } , how can I get dataList without write where twice? I don't want to do like this:

var totalCount = await knex.table('users').where('status',1).where('online', 1).count();
var data = await knex.table('users').where('status',1).where('online', 1).offset(0).limit(10).select()
return {
    totalCount: totalCount[0]['count']
    data: data
}

Thank you :)

Answer 1

You probably should use higher level library like Objection.js which has already convenience method for getting pages and total count.

You can do it like this with knex:

// query builder is mutable so when you call method it will change builders internal state
const query = knex('users').where('status',1).where('online', 1);

// by cloning original query you can reuse common parts of the query
const total = await query.clone().count();
const data = await query.clone().offset(0).limit(10);