Last active
March 31, 2016 08:18
-
-
Save anyong/559c91fb4b6add5d5ae9640f57f4f281 to your computer and use it in GitHub Desktop.
Bookshelf pagination plugin demo
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import Promise from 'bluebird'; | |
/** | |
* The default pagination options, these can be overridden | |
* by the user by passing an options object as the first argument | |
* to the Model#pagination call. The 'sort' column here can | |
* refer to any column in the Model's table or another table using | |
* the standard 'table.column' notation. | |
*/ | |
const defaultOptions = { | |
page: 1, | |
limit: 10, | |
sort: 'id', | |
order: 'ASC', | |
}; | |
/** | |
* Applies the query object or function passed through by the user | |
* This is basically copied from: | |
* | |
* https://github.com/tgriesser/bookshelf/blob/master/src/helpers.js#L41-L74 | |
*/ | |
function fullQuery (qb, method) { | |
if (typeof method === 'function') { | |
// `method` is a query builder callback. Call it on the query builder | |
// object. | |
Reflect.apply(method, qb, [qb]); | |
} else if (typeof method === 'object') { | |
// `method` is an object. Use keys as methods and values as arguments to | |
// the query builder. | |
for (const key in method) { | |
if (method.hasOwnProperty(key)) { | |
const target = Array.isArray(method[key]) ? method[key] : [method[key]]; | |
Reflect.apply(qb[key], qb, target); | |
} | |
} | |
} | |
return null; | |
} | |
/** | |
* Applies the pagination limit, offset, and ordering of results | |
*/ | |
function paginationQuery ({qb, method, limit, offset, orderBy}) { | |
fullQuery(qb, method); | |
qb.limit(limit); | |
qb.offset(offset); | |
qb.orderBy(...orderBy); | |
return null; | |
} | |
/** | |
* Exports a plugin to pass into the bookshelf instance, i.e.: | |
* | |
* import config from './knexfile'; | |
* import knex from 'knex'; | |
* import bookshelf from 'bookshelf'; | |
* import pagination from 'bookshelf-pagination-plugin'; | |
* | |
* const ORM = bookshelf(knex(config)); | |
* | |
* ORM.plugin(pagination); | |
* | |
* export default ORM; | |
* | |
* The plugin attaches one static and one instance method to the bookshelf | |
* Model object, both called 'paginate'. | |
* | |
* Model#paginate takes three parameters: | |
* | |
* - an options object as described above | |
* - a query builder object or function, the same as what would be passed | |
* to Model##query | |
* - the fetch options object, same as what gets passed to Model#fetchAll | |
* | |
* The method returns a promise that resolves with an object containing the | |
* found rows and pagination metadata: | |
* | |
* { | |
* total: (integer), | |
* page: (integer), | |
* offset: (integer), | |
* rows: (Array[Model]) | |
* } | |
* | |
* This allows for simple creation of paginated complex queries. | |
* See the cars-api-example.js gist for an example. | |
*/ | |
export default function paginate (bookshelf) { | |
bookshelf.Model = bookshelf.Model.extend({ | |
paginate (pagingOptions = defaultOptions, queryOptions, fetchOptions) { | |
const options = Object.assign({}, defaultOptions, pagingOptions); | |
const {limit, page, sort, order} = options; | |
const offset = limit * (page - 1); | |
const tableName = this.constructor.prototype.tableName; | |
const idAttribute = this.constructor.prototype.idAttribute ? | |
this.constructor.prototype.idAttribute : 'id'; | |
var orderBy; | |
if (sort.indexOf('.') > -1) { | |
orderBy = [tableName + sort, order]; | |
} else { | |
orderBy = [sort, order]; | |
} | |
const fetchPage = () => { | |
return this.constructor.forge() | |
.query(qb => paginationQuery({ | |
qb, limit, offset, orderBy, | |
method: queryOptions, | |
})) | |
.fetchAll(fetchOptions); | |
}; | |
const count = () => { | |
return this.constructor.forge() | |
.query(qb => fullQuery(qb, queryOptions)) | |
.count(`${tableName}.${idAttribute}`) | |
.then(total => { | |
return {total, page, limit, offset}; | |
}); | |
}; | |
return Promise.join(fetchPage(), count()) | |
.then(([rows, meta]) => { | |
return Object.assign({rows}, meta, {rowCount: rows.length}); | |
}); | |
}, | |
}, { | |
paginate (options, queryOptions, fetchOptions) { | |
return this.forge().paginate(options, queryOptions, fetchOptions); | |
}, | |
}); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import { Cars } from '../models'; | |
import app from '../app'; | |
app.use('/api/cars', (req, res) => { | |
const {page, limit, sort, order} = req.query; | |
return Cars.paginate({ | |
page, // 1 | |
limit, // 20 | |
sort, // 'manufacturer.id' | |
order // 'ASC' | |
}, qb => { | |
qb.innerJoin('manufacturers', 'manufacturers.id', 'cars.manufacturer_id'); | |
qb.where('cars.production_year', '>', '2000') | |
}, { | |
withRelated: ['manufacturer'] | |
}) | |
.then(result => { | |
res.header('X-Total-Count', result.total); | |
res.header('X-Total-Pages', Math.ceil(result.total / result.limit)); | |
if (result && result.rowCount > 0) { | |
return res.json(result.rows); | |
} | |
return res.sendStatus(404); | |
}); | |
}); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment