How to use COUNT aggregator inside HAVING clause in knex.js?

Question

let duplicates = await Books.query()
                  .select('book_id')
                  .groupBy('book_id')
                  .having(knex.count('book_id'), '>', 1);

Here I want to use the count aggregator inside the having clause. How can I do that inside knex.having() function?

Answer 1

Using a small snippet of knex.raw is probably the easiest way.

let duplicates = await Books.query()
                  .select('book_id')
                  .groupBy('book_id')
                  .having(knex.raw('count(book_id) > 1'));

Or if you want to properly escape your column name with backticks you can use a ?? positional argument

let duplicates = await Books.query()
                  .select('book_id')
                  .groupBy('book_id')
                  .having(knex.raw('count(??) > 1', 'book_id'));