Lucid pagination using offset instead of page?

Is there a way to easily extend .paginate() method so it accepts offset instead of page parameter?

Page/limit logic is ok until you have infinite loading lists (very often happens on native mobile apps).

I know I can simply use .offset(x).limit(x) but I want additional payload that .paginate() returns…

1 Like

I solved this with Lucid trait that overrides default paginate queryMacro… Not sure if this is best idea, but it works :slight_smile:

   const _ = use('lodash')
    const excludeAttrFromCount = ['order', 'columns', 'limit', 'offset']
    
    async function paginateOffset(offset = 0, perPage = 20) {
        const countByQuery = this.clone()
    
        /**
         * Copy the subQuery fn to the clone query. This will make sure
         * that build uses the extended query builder methods on the
         * cloned query too
         */
        countByQuery.subQuery = this.subQuery
    
        /**
         * Force cast page and perPage to numbers
         */
        offset = Number(offset)
        perPage = Number(perPage)
    
        /**
         * Remove statements that will make things bad with count
         * query, for example `orderBy`
         */
        countByQuery._statements = _.filter(countByQuery._statements, (statement) => {
            return excludeAttrFromCount.indexOf(statement.grouping) < 0
        })
    
        const counts = await countByQuery.count('* as total')
        const total = _.get(counts, '0.total', 0)
        const data = total === 0 ? [] : await this.offset(offset).limit(perPage)
    
        return {
            total: total,
            perPage: perPage,
            offset,
            data: data
        }
    }
    
    class PaginateWithOffset {
    
        register(Model, options) {
            Model.queryMacro('paginate', async function (page, limit, offset) {
                /**
                 * Apply all the scopes before fetching
                 * data
                 */
                this._applyScopes()
                let result
    
                if (typeof offset === 'undefined' || Number.isFinite(offset)) {
                    // run default logic
                    result = await this.query.paginate(page, limit)
                } else {
                    result = await paginateOffset.call(this.query, offset, limit)
                }
    
                /**
                 * Convert to an array of model instances
                 */
                const modelInstances = this._mapRowsToInstances(result.data)
                await this._eagerLoad(modelInstances)
    
                /**
                 * Pagination meta data
                 */
                const pages = _.omit(result, ['data'])
    
                /**
                 * Fire afterPaginate event
                 */
                if (this.Model.$hooks) {
                    await this.Model.$hooks.after.exec('paginate', modelInstances, pages)
                }
    
                /**
                 * Return an instance of active model serializer
                 */
                const Serializer = this.Model.resolveSerializer()
                return new Serializer(modelInstances, pages)
            })
        }
    }
    
    module.exports = PaginateWithOffset
     

By using this trait, you can paginate using page, limit combination like you used before. But if you need offset logic, just pass it as third param.

3 Likes