OffSet + Limit query returning wrong results with adonis / node.js

I need to do this query using adonis:

SELECT * FROM book_unit where book_id = 1 ORDER BY unit desc LIMIT 1 OFFSET 2

So, i do this function:

async procuraProximoSequence(bookId, offSet, maxRecords){
    console.log(offSet)
    console.log(maxRecords)
    const historicoQuery = BookUnit.query()
    .where('book_id', bookId)
    .orderBy(['unit', 'sequence'], 'asc')
    if(offSet !== null){
        historicoQuery.offset(offSet)
    }
    if(maximoRegistros !== null){
        historicoQuery.limit(maxRecords)
    }

    return await historicoQuery.paginate(1, 100)
}

This is the console.log() value of the offSet, maxRecords and the query that knex do:

2 
1 
  knex:query select count(*) as "total" from "book_unit" where "book_id" = ? limit ? offset ? undefined +8ms

knex:bindings [ 1, 1, 2 ] undefined +111ms

If i print the result of the query i receive no rows:

VanillaSerializer {
  rows: [],
  pages: { total: 0, perPage: 100, page: 1, lastPage: 0 },
  isOne: false }

The expected row of the query is this image:

2 Likes

It looks like i can’t use .limit() with .paginate()

In my case, i do this correction:

    async procuraProximoSequence(bookId, offSet, maximoRegistros){
        console.log(bookId, offSet, maximoRegistros)
        const historicoQuery = BookUnit.query()
        .where('book_id', bookId)
        .orderBy(['unit', 'sequence'], 'asc')
        if(offSet !== null){
            historicoQuery.offset(offSet)
        }
        if(maximoRegistros !== null){
            return await historicoQuery.limit(maximoRegistros).fetch()
        }

        return await historicoQuery.paginate(1, 100)
    }

and now everything works

2 Likes