How to count results passing a subquery in Knex?

Question

I have a big query that selects multiple columns from multiple tables, and I would like to know how many records there are (doing a count).

I cannot get the length of the results because I also add .offset and .limit to the query.

Is there a way I can generate the following

SELECT COUNT(*) FROM (
  knex query
) as count

with knex? How?

(version: 0.11.10)

Answer 1

You must be looking for this

const knex = require('knex')({ client: 'pg' })

const builder = knex
    .count('t.* as count')
    // You actually can use string|function with this = knex builder|another knex builder
    .from(function () {
        // Your actual query goes here
        this
            .select('*')
            .from('users')
            .whereNull('some_condition', 'some_value')
            .as('t') // Alias for your DB (For example Postgres requires that inner query must have an alias)
    })
    .first()

console.log(builder.toString()) // Prints your query
// => select count("t".*) from (select * from "users" where "removed_at" is null) as "t" limit 1