CODEWITHSUNDEEP
javascriptknex.js
BrunoLM
BrunoLM

Reputation: 100381

How to count results passing a subquery in Knex?

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)

Upvotes: 1

Views: 5890

Answers (3)

Nathan S
Nathan S

Reputation: 114

The following code returns a count given a complex inner query:

async getTotalCount() {
  const clonedKnexObject = knexObject.clone();
  clonedKnexObject.limit(999999);
  const countQuery = knex.from(clonedKnexObject.as('a')).count();
  const response = await dbQuery(countQuery.toSQL().toNative());
  return response[0]['count(*)'];
}

Note: I needed to set .limit(999999) because it has a much lower existing limit I need to overwrite.

Upvotes: 0

coockoo
coockoo

Reputation: 2392

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

Upvotes: 2

Quentin Watt
Quentin Watt

Reputation: 11

Got this from the documentation on the current site.

knex('users').count('active as a')
Outputs:
select count(`active`) as `a` from `users`

Knex documentation v 0.13.0

searching for "select count" will give you a few more examples.

Upvotes: 0

Related Questions