Attempting to implement cursor based pagination - and I would like to know the proper way to call it with Knex for Postgres.
Here is the standard statement:
SELECT
FROM
WHERE (timestamp, id) > (cursor.age, cursor.id)
ORDER BY timestamp ASC, id ASC
LIMIT
And my attempt with Knex:
const knexResult = await knex({ 'table_name' })
.select(columns)
.where('created_at', '>', cursor.timestamp)
.andWhere('id', '>', cursor.id)
.orderBy(['timestamp', 'id'])
.limit(first)
return knexResult;
Is this the proper way to call it - it seems not correct... I am trying to avoid knex.raw
Edit
The SQL standard for the statement (x,y) > (a,b) is true if:
(x > a or (x = a and y > b))
The code above gives no-results because I believe its trying to match both where clauses.
You can implement this boolean condition (x > a or (x = a and y > b)) with knex.
const knexResult = await knex({ 'table_name' })
.select(columns)
.where('created_at', '>', cursor.timestamp)
.orWhere((inner) =>
inner.where('created_at', '=', cursor.timestamp)
.andWhere('id', '>', cursor.id)
)
.orderBy(['timestamp', 'id'])
.limit(first);
return knexResult;
It will produce:
Select `col1`, `col2` from `table_name` where `created_at` > '12323112' or (`created_at` = '12323112' and id > '23')
Order by `timestamp`, `id`
Limit 1