felixmosh / knex-paginate

An extension of Knex's query builder with `paginate` method that will help with your pagination tasks.

Knex-paginate

Extension of Knex's query builder with paginate method that will help with your pagination tasks.

How to set up

To use this lib, first you will have to install it:

npm i knex-paginate --save
// or
yarn add knex-paginate

Then, add the following lines to your Knex set up:

const knex = require('knex')(config);

const { attachPaginate } = require('knex-paginate');
attachPaginate();

Function definition

.paginate(params: IPaginateParams): Knex.QueryBuilder<any, IWithPagination<TResult>>;

interface IPaginateParams {
  perPage: number,
  currentPage: number,
  isFromStart?: boolean,
  isLengthAware?: boolean,
}

interface IWithPagination<T = any> {
  data: T;
  pagination: IPagination;
}

interface IPagination {
  total?: number;
  lastPage?: number;
  currentPage: number;
  perPage: number;
  from: number;
  to: number;
}

How to use

Example

const result = await knex('persons')
   .paginate({ perPage: 10, currentPage: 2 });
// result.data - will hold persons data
// result.pagination - will hold pagination object

pagination object

Key	Value
perPage	Items per page.
currentPage	Current page number.
from	Counting ID of the first item of the current page.
to	Counting ID of the last item of the current page.

Returned if isLengthAware == true or currentPage == 1 or isFromStart == true:

Key	Value
total	Total items that the full query contains.
lastPage	Last page number.

This lib got inspiration from knex-paginator.

knex-paginate's Issues

How to paginate many to one relations?

Say for instance that I'm building an endpoint to list users from a table, and each user can have multiple roles. This sounds as simples as:

SELECT u.*, r."role" FROM users as u LEFT JOIN roles as r ON r.uid = u.id

However if I use this library to paginate that query, it will look like this:

SELECT u.*, r."role" FROM users as u LEFT JOIN roles as r ON r.uid = u.id LIMIT 10 OFFSET 0

Which is semantically correct, but not quite right for my use case. With SQL one way to fix this is by having the query like the following:

SELECT u.*, r."role" FROM (SELECT u.* FROM users as u [WHERE CLAUSE] LIMIT 10 OFFSET 0) as u LEFT JOIN roles as r ON r.uid = u.id

Is there any way to do something similar using this library?

Incomplete pagination information when passing any pageNumber other than 1.

Passing the below line (have also hardcoded integers from 2-66 and same result), the pagination results will only partially generate such that total, lastPage, prevPage, nextPage are missing. Works as intended if pageNumber = 1 is provided. knex information is correct regardless.

Working from Windows 10 with knex-paginate 8.18.0 in my express application.

query.paginate({ perPage: 100, currentPage: req.query.page || 1 })
query.paginate({ perPage: 100, currentPage: 2 })

Segfault

The paginate functionality works as expected when the application is run locally. But, when the paginate function is executed within docker container it throws a SEGFAULT exception. What could be the reason for this?

Support underscore?

Does this library support underscores to be more consistent with the rest of our API?

Paginate Method Breaks Ability to Extend Knex Module

Thanks for the module! It's really helpful. I ran into one issue where I wanted to further extend knex for my use case and chain something to your .paginate() method but was unable to because it does not inherit the knex prototypes.

Example:

pg.js

import knex from 'knex';
import knexPaginate from 'knex-paginate';
import attachRoleHandler from './knexRoles.js'

knexPaginate.attachPaginate()
attachRoleHandler()

TestKnex.js

// Works fine
async function doAsync () {
    const result = await User
        .getById(1)
        .paginate({perPage: 10, currentPage:1})
        // .enforceRoles(role, roleSchema)
    console.log(result)
}

// Works fine
async function doAsync () {
    const result = await User
        .getById(1)
        // .paginate({perPage: 10, currentPage:1})
        .enforceRoles(role, roleSchema)
    console.log(result)
}

// ERROR
async function doAsync () {
    const result = await User
        .getById(1)
        .paginate({perPage: 10, currentPage:1})
        .enforceRoles(role, roleSchema)
    console.log(result)
}

With the error being

(node:41960) UnhandledPromiseRejectionWarning: TypeError: User.getById(...).paginate(...).enforceRoles is not a function

I really need it to follow your pagination so I don't want to modify my own module to go before. Is there anyway you could allow the chain to continue?

pagination.total in result is actually string

Hi Felix!

Thank you for your package!
Is it intended or error that pagination.total is actually string, not number in result?

    "pagination": {
        "total": "806792",
        "lastPage": 26894,
        "perPage": 30,
        "currentPage": 1,
        "from": 0,
        "to": 30
    }

Feature: .toString() off of pagination()

Previously we were testing that queries worked by asserting their generated SQL query output. I've noticed that using pagination doesn't allow for a .toString() option.

This observation that ".toString()" isn't available is true right? If so maybe I could work on a PR for it? Would be helpful from a testing perspective, but also for debugging I believe.

Can't extend QueryBuilder with existing method ('paginate').

I'm getting this error when starting my code:

Error: Can't extend QueryBuilder with existing method('paginate').
at Function.Builder.extend (/apps/api-cantinho-da-limpeza/node_modules/knex/lib/query/querybuilder.js:1756:11)
at Object.extend (/apps/api-cantinho-da-limpeza/node_modules/knex/lib/knex-builder/Knex.js:30:18)
at attachPaginate (/apps/api-cantinho-da-limpeza/node_modules/knex-paginate/lib/index.js:78:21)
at materials (/apps/api-cantinho-da-limpeza/src/routes/materials.ts:7:1)
at Object. (/apps/api-cantinho-da-limpeza/src/routes/materials.ts:9:23)
at Module._compile (node:internal/modules/cjs/loader:1254:14)
at Object.F (/apps/api-cantinho-da-limpeza/node_modules/@esbuild-kit/cjs-loader/dist/index.js:1:941)
at Module.load (node:internal/modules/cjs/loader:1117:32)
at Module._load (node:internal/modules/cjs/loader:958:12)
at Module.require (node:internal/modules/cjs/loader:1141:19)

I made the imports and stationed in the way specified in the documentation
``
import { FastifyInstance } from "fastify";
import { z } from "zod";
import { knex } from "../database"
import crypto from "node:crypto";
import { attachPaginate } from 'knex-paginate'

attachPaginate();

``

I'm using these dependencies:
"dependencies": {
"dotenv": "^16.0.3",
"fastify": "^4.14.1",
"knex": "^2.4.2",
"knex-paginate": "^3.1.0",
"pg": "^8.10.0",
"zod": "^3.21.4"
},

Sorting support

Thanks for the library, comparing to other stacks notice they do support for sorting.

https://github.com/jhipster/jhipster-sample-app-nodejs/blob/master/server/src/domain/base/pagination.entity.ts#L29
https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Pageable.html

Getting result as array, not object

Hi,

I'm using this package for my project but getting the result as an array. Could you have a look

this is not working, even I print the query, it's correct

const { attachPaginate } = require('knex-paginate');
attachPaginate();

async function(query: any, options: any) {
    query.paginate({ perPage: options.limit || 50, currentPage: 1, isLengthAware: true });

    const result = await query

    // result is an array
    return {
        total: result.pagination.total,
        data: result.data,
    };
}

this is working well

async function(query: any, options: any) {
    // FIXME: need to calculate current page from offset inside options
    const paging = query.paginate({ perPage: options.limit || 50, currentPage: 1, isLengthAware: true });

    const result = await paging

    return {
        total: result.pagination.total,
        data: result.data,
    };
}

npm package version:
knex: 0.21.0
knex-paginate: 1.2.0

attachPaginate doest work with fastify-knexjs

im currently using the integration of knexjs with fastify but the plugin doesn't attach the function using the current code, im open to suggestions:

module.exports = fp(async function (fastify, opts) { const options = { client: 'postgresql', connection: fastify.conf.dbAccess(), migrations: { tableName: 'migrations' }, pool: { min: 2, max: 10 } } fastify.register(require('fastify-knexjs'), options, (err) => console.error(err) ) attachPaginate() }, { name: 'knex', dependencies: ['configuration'] })

i think it will be nice to have the optional parameter for knex instance to do the extends instead of asume you are using the package directly

Request: Pass params to attachPaginate for global usage

I'd love to be able to pass isLengthAware: true to attachPaginate() as a param and have this take effect globally on all usages of paginate.

Error with mssql

Hi, and thank you for your library !

There is a bug with the mssql driver during the count request because order by are not supported in sub-queries

In order fix it you can add ".clearOrder()" in line 42: it will remove the (useless) orderBy during the count request

paginate() does not work when using modify method within knex query

Hi Guys I am trying to set pagination only if the page filter is passed. Otherwise should return the regular response from the query.

If filter.page = 1

Expected:
{
data:[{...},{...},{...},{...}],
pagination:[{...},{...},{...},{...}]
}

But i am getting

[
{ user1 },
{ user2 },
{ user3 },
.
.
.
{ usern },
]

const db = require('knex');
const query = db('users');
const users = await query
  .clone()
  .distinct()
  .select('users.*')
  .orderBy('users.id', 'DESC')
  .modify((query) => {
    if (filters.page) {
      if (!db.queryBuilder().paginate) {
        attachPaginate();
      }
      query = query.paginate({ perPage: 20, currentPage: filters.page, isLengthAware: true });
    }
  });
return users;

Any help will be much appreciate it

Request: Pass parameter to pagination to disable it

It would be very useful if you want to get all the records without pagination by passing a parameter. (and keep the same data structure)

Must use paginate last or it breaks

Whenever I try to run a query after paginate has been called it fails, for example:

const query = db('users')
    .select('id', 'username')
    .orderBy('username')
    .paginate({})
    .where({ admin: false });

Results in the following error:

UnhandledPromiseRejectionWarning: TypeError: db(...).select(...).orderBy(...).paginate(...).where is not a function

Passing TData causes IPagination to never return ILengthAwarePagination

// works

const result = await db('users').select('*').paginate({
        perPage: 10,
        currentPage: 1,
        isLengthAware:true,
      })
    );

result.pagination.total // ✅

// doesn't work
const result = await db('users').select('*').paginate<User[]>({
        perPage: 10,
        currentPage: 1,
        isLengthAware:true,
      })
    );

result.pagination.total  // ❌ Property 'total' does not exist on type 'IBasePagination'.

isLengthAware issue

I'm using Oracle database and when I passed the argument 'isLengthAware' as true, it would return an error on the query I was running.

"select * from (select count(*) total from (select * from user) __count__query__) where rownum <= :1 - ORA-00911: invalid character"

I decided to take a look at the library and identified two things that caused the problem. First one was at line 44 .as('__count__query__'), and the other was at line 57 where you destructure the object to grab the total, but in my case the total was returning as upperCase.
Would you mind giving me an explanation to the .as('__ count __query __'),, or maybe another solution to this problem, without modifying the library?
The fix I identified was by removing the .as() and making the total upperCase, is it something that would break in other types of databases?

Let me know and I'll open a PR with my fix.
Thanks!

Invalid usage of operation "next" in the FETCH statement

Hello, would appreciate any debugging advice, apologies in advance if I've missed something.

I'm running the following knex query:

    getPrompts(companyId: number, currentPage: number, pageSize: number) {
        const knex = this.databaseService.getConnection()
        return knex("prompts")
            .where("company_id", companyId)
            .paginate<Prompt[]>({
                currentPage: currentPage,
                perPage: pageSize,
                isLengthAware: true
            })
    }

And calling getPrompts with getPrompts(1, 2, 2) results in the query:
select * from [prompts] where [company_id] = ? offset ? rows fetch next ? rows only trx3

Which throws the knex error: [ExceptionsHandler] select * from [prompts] where [company_id] = @p0 offset @p1 rows fetch next @p2 rows only - Invalid usage of the option next in the FETCH statement.

Is there something happening wrong syntax wise in the query builder? Not sure why the next option is used. I'm using a mysql database connection.

Additionally:
This is similar to trying to extend a .paginate off of an existing lookup method. I've noticed you have a "getById" method in your test, but it's not referenced anywhere. Maybe after solving this I could write a PR that tests for this?

Feature request: add next page and prev page numbers

Thanks for amazing package.

Pagination should return previous page and next page .

if current page is 1 than previous page should be null
if last page is null the next page should be null

eg:
{
total: number
currentPage: number
perPage: number
prev: number | null
next: number | null
}

it will give flexibility and ease to use for pagination in tables on the front end.

I did add the next and prev page in the code but I cannot pass the tests so I am a little confused. can I submit the PR without tests I am not sure if I can fix those tests

this is how I did

return this.client.transaction(async (trx) => {
     const result = await this.transacting(trx);
     let lastPage = null;
     if (shouldFetchTotals) {
       const countResult = await countQuery.transacting(trx);
       const total = +(countResult.TOTAL || countResult.total || 0);
       lastPage = Math.ceil(total / perPage);
       pagination = {
         total,
         lastPage: lastPage,
       };
     }

     // Add pagination data to paginator object
     pagination = postProcessResponse({
       ...pagination,
       perPage,
       currentPage,
       prev: currentPage  >1 ? currentPage - 1 : null,
       next: currentPage < lastPage ? currentPage + 1 : null,
       from: offset,
       to: offset + result.length,
     });

     return { data: result, pagination };
   });

paginate on raw knex?

any feature to enable pagination in knex.raw() ?
i have tried use

knex
.raw(`select * from data`).
.paginate({ perPage: 10, currentPage:  1 })

but i get eror like this

{ "statusCode": 500, "error": "Internal Server Error", "message": "knex.raw(...).paginate is not a function" }

'total' and 'lastPage' properties returning the NaN value.

Good morning, I'm having a problem when my search returns 0 results. This generates fake pages when using 'datatables', anyway I solved it by handling this on the frontend. But I believe that the ideal would be to return the value 0 when no record is found. I immediately apologize if I made a mistake in using this tool.

output example obtained:

{
  data: [],
  pagination: {
    total: NaN,
    lastPage: NaN,
    perPage: '50',
    currentPage: '1',
    from: 0,
    to: 0
  }
}

Return type not matching knex return type

When providing data types, knex expects a record, while paginate expects record array. As the result, paginate is not using correct data type returned

const a = await knex_udrd.select().from<{id:string}>('users')
// per typescript compiler, a is of type { id: string} []; this matches actual data returned.


const b = await knex_udrd.select().from<{id:string}>('users').paginate({perPage: 2,currentPage:1})
// per typescript b is of type { data: {id: string}, paginate: { .... } }; however data is in { data: {id: string}[], paginate: { .... } } format

I propose that we modify knex-paginate types as follows (this will also require tests to be re-written):

interface IWithPagination<Data, TParams = IPaginateParams> {
  data: Data[]; // added array
  pagination: IPagination<TParams>;
}

Error: Can't extend QueryBuilder with existing method ('paginate').

Looking into replacing knex-paginator with this, and it looks pretty easy. However, I am running into an issue. All of my routes are in separate files:

const someRoute = require('./some');
router.use('/some', someRoute);
const otherRoute = require('./other');
router.use('/other', otherRoute);

Then in the route files I have:

const knexConfig = require('../knexfile');
const knex = require('knex')(knexConfig);
const { attachPaginate } = require('knex-paginate');
attachPaginate();

When I just have 1 route it works fine, but when I have all of them loaded, it gives the following error:

[dev.server] Error: Can't extend QueryBuilder with existing method ('paginate').
[dev.server]     at Function.Builder.extend (/var/www/user-audit/server/node_modules/knex/lib/query/builder.js:1287:11)
[dev.server]     at Object.extend (/var/www/user-audit/server/node_modules/knex/lib/knex.js:63:18)
[dev.server]     at attachPaginate (/var/www/user-audit/server/node_modules/knex-paginate/lib/index.js:4:21)
[dev.server]     at Object.<anonymous> (/var/www/user-audit/server/routes/campus.js:7:1)
[dev.server]     at Module._compile (internal/modules/cjs/loader.js:774:30)
[dev.server]     at Object.Module._extensions..js (internal/modules/cjs/loader.js:785:10)
[dev.server]     at Module.load (internal/modules/cjs/loader.js:641:32)
[dev.server]     at Function.Module._load (internal/modules/cjs/loader.js:556:12)
[dev.server]     at Module.require (internal/modules/cjs/loader.js:681:19)
[dev.server]     at require (internal/modules/cjs/helpers.js:16:16)
[dev.server]     at Object.<anonymous> (/var/www/user-audit/server/routes/index.js:43:21)
[dev.server]     at Module._compile (internal/modules/cjs/loader.js:774:30)
[dev.server]     at Object.Module._extensions..js (internal/modules/cjs/loader.js:785:10)
[dev.server]     at Module.load (internal/modules/cjs/loader.js:641:32)
[dev.server]     at Function.Module._load (internal/modules/cjs/loader.js:556:12)
[dev.server]     at Module.require (internal/modules/cjs/loader.js:681:19)

Am I doing something wrong? If so, what should I do to resolve the issue? The code was working fine with knex-paginator with minor tweaks.

can't use 'paginate' when together using group by on knex

issued an error

select count(distinct undefined) as total from transaction as t limit 1

the version that I am using
"knex": "^0.20.4",
"knex-paginate": "^1.1.0",

this is my query
await knex.select('id').from('transaction as t').groupBy(knex.raw('date(t.created_at)')).orderBy('id','desc').paginate({perPage: 10, currentPage: 1})

is this a bug?

`perPage === 0` behavior

Hello! Can u add case for perPage === 0? So, this mean, that page should contain all entries. Its very useful case. For example, Java's Spring pagination functionality works the same. Its the only way to get all entries in methods, that wrapped by .paginate({ perPage, currentPage });

knex-paginate/lib/index.js

Line 4 in fa6b11b

function paginate({ perPage = 10, currentPage = 1, isFromStart = false, isLengthAware = false }) {

usage with cte queries

Hi I ran into an issue when creating a cte query and using it with pagination i get syntax errors.

I have tested my main query sepeartely and this error only happens using knex-paginate.

error message:
- Incorrect syntax near ')'

happy to share the query output with you :D

`.paginate` is not a function in KnexJS 3+

Summary

Upgraded knexjs to ^3.0.0, functions that uses .paginate() now returns this error:

this.transaction.select(...).from(...).leftJoin(...).leftJoin(...).leftJoin(...).leftJoin(...).leftJoin(...).leftJoin(...).leftJoin(...).where(...).groupBy(...).modify(...).orderByRaw(...).paginate is not a function

Additional Notes

• No changes were done except to upgrade knexjs