custom the pagination metadata by developer

[arden]

about the pageination metadata, this can custom by developer self?

[anyong]

What metadata do you want besides the page count and row count?

[arden]

i'm want to Pagination's metadata as below:

pagination: {
    page: 1,           // current page
    pageSize: 20,    // size for per page.
    total: 10,          // total rows
    pages: 1,         // total pages
    prePage: 1,     // pre page
    nextPage: 1    // next page
}

Because developers will have different habit for you, or, In order to legacy system. Now, i can only fork the code and modify by self.

[arden]

Below is my modified version that can custom pagination metadata by developer self:

'use strict';

import Promise from 'bluebird';
import { remove as _remove, assign as _assign } from 'lodash';

const DEFAULT_LIMIT = 10;
const DEFAULT_OFFSET = 0;
const DEFAULT_PAGE = 1;

let DEFAULT_METADATA = {
    rowCount: 'rowCount',
    pageCount: 'pageCount',
    page: 'page',
    pageSize: 'pageSize',
    prePage: 'prePage',
    nextPage: 'nextPage',
    offset: 'offset',
    limit: 'limit'
};

/**
 * Exports a plugin to pass into the bookshelf instance, i.e.:
 *
 *      import config from './knexfile';
 *      import knex from 'knex';
 *      import bookshelf from 'bookshelf';
 *
 *      const ORM = bookshelf(knex(config));
 *
 *      ORM.plugin('bookshelf-pagination-plugin');
 *
 *      export default ORM;
 *
 * The plugin attaches two instance methods to the bookshelf
 * Model object: orderBy and fetchPage.
 *
 * Model#orderBy calls the underlying query builder's orderBy method, and
 * is useful for ordering the paginated results.
 *
 * Model#fetchPage works like Model#fetchAll, but returns a single page of
 * results instead of all results, as well as the pagination information
 *
 * See methods below for details.
 */
module.exports = function paginationPlugin (bookshelf, customMetaData) {
    let _customMetaData = customMetaData || DEFAULT_METADATA;
    let orignCustomMetaData = _customMetaData;

    /**
     * @method Model#orderBy
     * @since 0.9.3
     * @description
     *
     * Specifies the column to sort on and sort order.
     *
     * The order parameter is optional, and defaults to 'ASC'. You may
     * also specify 'DESC' order by prepending a hyphen to the sort column
     * name. `orderBy("date", 'DESC')` is the same as `orderBy("-date")`.
     *
     * Unless specified using dot notation (i.e., "table.column"), the default
     * table will be the table name of the model `orderBy` was called on.
     *
     * @example
     *
     * Cars.forge().orderBy('color', 'ASC').fetchAll()
     *    .then(function (rows) { // ...
     *
     * @param sort {string}
     *   Column to sort on
     * @param order {string}
     *   Ascending ('ASC') or descending ('DESC') order
     */
    function orderBy (sort, order) {
        const tableName = this.constructor.prototype.tableName;
        const idAttribute = this.constructor.prototype.idAttribute ?
            this.constructor.prototype.idAttribute : 'id';

        let _sort;

        if (sort && sort.indexOf('-') === 0) {
            _sort = sort.slice(1);
        } else if (sort) {
            _sort = sort;
        } else {
            _sort = idAttribute;
        }

        const _order = order || (
                (sort && sort.indexOf('-') === 0) ? 'DESC' : 'ASC'
            );

        if (_sort.indexOf('.') === -1) {
            _sort = `${tableName}.${_sort}`;
        }

        return this.query(qb => {
            qb.orderBy(_sort, _order);
        });
    }

    /**
     * Similar to {@link Model#fetchAll}, but fetches a single page of results
     * as specified by the limit (page size) and offset or page number.
     *
     * Any options that may be passed to {@link Model#fetchAll} may also be passed
     * in the options to this method.
     *
     * To perform pagination, you may include *either* an `offset` and `limit`, **or**
     * a `page` and `pageSize`.
     *
     * By default, with no parameters or missing parameters, `fetchPage` will use an
     * options object of `{page: 1, pageSize: 10}`
     *
     *
     * Below is an example showing the user of a JOIN query with sort/ordering,
     * pagination, and related models.
     *
     * @example
     *
     * Car
     * .query(function (qb) {
     *    qb.innerJoin('manufacturers', 'cars.manufacturer_id', 'manufacturers.id');
     *    qb.groupBy('cars.id');
     *    qb.where('manufacturers.country', '=', 'Sweden');
     * })
     * .orderBy('-productionYear') // Same as .orderBy('cars.productionYear', 'DESC')
     * .fetchPage({
     *    pageSize: 15, // Defaults to 10 if not specified
     *    page: 3, // Defaults to 1 if not specified
     *
     *    // OR
     *    // limit: 15,
     *    // offset: 30,
     *
     *    withRelated: ['engine'] // Passed to Model#fetchAll
     * })
     * .then(function (results) {
     *    console.log(results); // Paginated results object with metadata example below
     * })
     *
     * // Pagination results:
     *
     * {
     *    models: [<Car>], // Regular bookshelf Collection
     *    // other standard Collection attributes
     *    ...
     *    pagination: {
     *        rowCount: 53, // Total number of rows found for the query before pagination
     *        pageCount: 4, // Total number of pages of results
     *        page: 3, // The requested page number
     *        pageSze: 15, // The requested number of rows per page
     *
     *  // OR, if limit/offset pagination is used instead of page/pageSize:
     *        // offset: 30, // The requested offset
     *        // limit: 15 // The requested limit
     *    }
     * }
     *
     * @param options {object}
     *    The pagination options, plus any additional options that will be passed to
     *    {@link Model#fetchAll}
     * @returns {Promise<Model|null>}
     */
    function fetchPage (options = {}) {
        const {page, pageSize, limit, offset, meta, ...fetchOptions} = options;

        let usingPageSize = false; // usingPageSize = false means offset/limit, true means page/pageSize
        let _page;
        let _pageSize;
        let _limit;
        let _offset;

        _customMetaData = meta || orignCustomMetaData;

        function ensureIntWithDefault (val, def) {
            if (!val) {
                return def;
            }

            const _val = parseInt(val);
            if (Number.isNaN(_val) || _val <= 0) {
                return def;
            }

            return _val;
        }

        if (!limit && !offset) {
            usingPageSize = true;

            _pageSize = ensureIntWithDefault(pageSize, DEFAULT_LIMIT);
            _page = ensureIntWithDefault(page, DEFAULT_PAGE);

            _limit = _pageSize;
            _offset = _limit * (_page - 1);
        } else {
            _pageSize = _limit; // not used, just for eslint `const` error
            _limit = ensureIntWithDefault(limit, DEFAULT_LIMIT);
            _offset = ensureIntWithDefault(offset, DEFAULT_OFFSET);
        }

        const tableName = this.constructor.prototype.tableName;
        const idAttribute = this.constructor.prototype.idAttribute ?
            this.constructor.prototype.idAttribute : 'id';

        const paginate = () => {
            // const pageQuery = clone(this.query());
            const pager = this.constructor.forge();

            return pager

                .query(qb => {
                    _assign(qb, this.query().clone());
                    qb.limit.apply(qb, [_limit]);
                    qb.offset.apply(qb, [_offset]);
                    return null;
                })

                .fetchAll(fetchOptions);
        };

        const count = () => {
            const notNeededQueries = [
                'orderByBasic',
                'orderByRaw',
                'groupByBasic',
                'groupByRaw'
            ];
            const counter = this.constructor.forge();

            return counter

                .query(qb => {
                    _assign(qb, this.query().clone());

                    // Remove grouping and ordering. Ordering is unnecessary
                    // for a count, and grouping returns the entire result set
                    // What we want instead is to use `DISTINCT`
                    _remove(qb._statements, statement => {
                        return (notNeededQueries.indexOf(statement.type) > -1) ||
                            statement.grouping === 'columns';
                    });
                    qb.countDistinct.apply(qb, [`${tableName}.${idAttribute}`]);
                })

                .fetchAll()

                .then(result => {
                    /*
                    const metadata = usingPageSize ?
                    {page: _page, pageSize: _limit} :
                    {offset: _offset, limit: _limit};
                    */
                    const metadata = {};
                    if (usingPageSize) {
                        metadata[_customMetaData.page] = _page;
                        metadata[_customMetaData.pageSize] = _pageSize;
                    } else {
                        metadata[_customMetaData.offset] = _offset;
                        metadata[_customMetaData.limit] = _limit;
                    }

                    if (result && result.length == 1) {
                        // We shouldn't have to do this, instead it should be
                        // result.models[0].get('count')
                        // but SQLite uses a really strange key name.
                        const count = result.models[0];
                        const keys = Object.keys(count.attributes);
                        if (keys.length === 1) {
                            const key = Object.keys(count.attributes)[0];
                            //metadata.total = parseInt(count.attributes[key]);
                            metadata[_customMetaData.rowCount] = parseInt(count.attributes[key]);
                        }
                    }

                    return metadata;
                });
        };

        return Promise.join(paginate(), count())
            .then(([rows, metadata]) => {
                const pages = Math.ceil(metadata[_customMetaData.rowCount] / _limit);
                let prePage = 1;
                if (_page > 1) {
                    prePage = _page - 1;
                }
                let nextPage = pages;
                if (_page < pages) {
                    nextPage = _page + 1;
                }
                metadata[_customMetaData.pageCount] = pages;
                metadata[_customMetaData.prePage] = prePage;
                metadata[_customMetaData.nextPage] = nextPage;
                //const pageData = _assign(metadata, {pages, prePage, nextPage});
                const pageData = metadata;
                return _assign(rows, {pagination: pageData});
            });
    }

    if (typeof bookshelf.Model.prototype.orderBy === 'undefined'){
        bookshelf.Model.prototype.orderBy = orderBy;
    }

    bookshelf.Model.prototype.fetchPage = fetchPage;

    bookshelf.Model.fetchPage = function (...args) {
        return this.forge().fetchPage(...args);
    }

    bookshelf.Collection.prototype.fetchPage = function (...args) {
        return fetchPage.apply(this.model.forge(), ...args);
    };

}

Use for Example:

const PAGINATION_METADATA = {
    rowCount: 'total',
    pageCount: 'pages',
    page: 'page',
    pageSize: 'pageSize',
    prePage: 'prePage',
    nextPage: 'nextPage'
};
tujiaoBookshelf.plugin('bookshelf-page', PAGINATION_METADATA);

OR in Query directly:

        let result = await Quiz.query({where: whereQuery})
            .orderBy(`-id`)
            .fetchPage({
                pageSize: pageSize,
                page: page,
                meta: {
                    rowCount: 'total',
                    pageCount: 'pages',
                    page: 'page',
                    pageSize: 'pageSize',
                    prePage: 'prePage',
                    nextPage: 'nextPage'
                }
            });
        let quizs = result.models;

Above code is Modified version. Now can custom pagination metadata by developer self.

[anyong]

I think the config option would work. Just need to think about how it should look. I'm thinking it should be an array of the key names you want.