records missing if one of orderBy is un-unique date column

[idododu]

Description:

we have 12 records in a datable, crated column is date-time column, record 10, 11, 12 have the same created time because it is generated by db seeds. when we visit page 1(items 1-10), it is correct when click next btn to visit page 2(items 11-12), it returns empty.

Root Cause:

where condition "created" = '2019-08-15T09:17:07.077Z' will not work because string is not equal to date-time column value

sql generated by objection-cursor is:

select
    *
from
    "Record"
where
    "created" < '2019-08-15T09:17:07.077Z'
    or (
        "created" = '2019-08-15T09:17:07.077Z'
        and ("id" < 'uid-03')
    )
order by
    "created" desc,
    "id" desc
limit 10

Workaround:

when sorting by date column, use a to_char instead

let query = Record.query()
  .select('*')
  .select(Record.raw('to_char("created", 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') as "createdDate"'))
  .sortBy('createdDate', 'desc')
  .sortBy('id', 'desc')

return query.cursorPage(params)

Potential Solution:

// lib/serialize.js
function serializeCursor(ops, item) {
    const arr = item ? ops.map(({prop}) => {
        const val = get(item, prop, null);
        if (typeof val === 'undefined') {
            throw new Error(`Item is missing required property: '${prop}'`);
        }
                // add 'date:' prefix for date-time column
               if (val instanceof Date) {
                 return 'date:' + base64url(JSON.stringify(val));
               }
               else {
                 return base64url(JSON.stringify(val));
               }        
    }) : [];
    return arr.join('.');
}

function deserializeCursor(ops, cursor = '') {
    const vals = cursor.split('.').map(str => str && JSON.parse(base64url.decode(str)));
    return cursor ?
        ops.reduce((acc, {prop}, i) => {
                        // decode date-time column
                        const val = vals[i];
                        if (val.indexOf('date:') === 0) {
                           val = new Date(val.replace('date:', ''))
                        }
            set(acc, prop, val);
            return acc;
        }, {}) :
        null;
}

Others:

// dataset example as below
const dataset = [
  // ...
  // the 10th item
  { id: 'uid-03', created: '2019-08-15T09:17:07.077Z' },
  { id: 'uid-02', created: '2019-08-15T09:17:07.077Z' },
  { id: 'uid-01', created: '2019-08-15T09:17:07.077Z' },
]

[olavim]

What database do you use? Please also show me your SQL schema.

[olavim]

I was kind of able to reproduce this using MySQL. Can you check if https://github.com/olavim/objection-cursor/commit/e2cbfe0b2f77a56c5a78aee64df5ee12a7b212d0 works for you.

[idododu]

I was kind of able to reproduce this using MySQL. Can you check if e2cbfe0 works for you.

Confirmed on local, it is fixed. By the way, my database is postgres

[olavim]

Fix is included in release 1.0.0-alpha.2.