Sort Bookshelf.js results with .orderBy()

Question

I am working on a personal project to learn Node.js + express + Bookshelf.js. Where do I build queries? In particular, how do I simply set an 'ORDER BY' or 'WHERE' in the following code?

var Accounts = require('../collections/accounts').collection;

new Accounts().fetch({
    withRelated: ['folders']
}).then(function(collection) {
    // process results
});

I would like to learn Bookshelf.js because it seems to offer the features I am used to with Laravel's Elequent (PHP), such as polymorphic relationships and sub expressions. However, I'm finding that the documentation is not very in-depth and trying to find examples is near impossible.

Thanks in advance for any help.

Robin

if you want to write SQL code, why do you use ORM? [node-mysql](https://github.com/felixge/node-mysql) and similar libraries would fit better. — alex, Feb 27 '14 at 12:14
Thank you for the comment and suggestion. I'd like to get away from writing raw MySQL code. I'll check that library out. — user1094128, Feb 27 '14 at 12:43

score 17 · Accepted Answer · answered Feb 27 '14 at 12:47

Ah just found the answer to my question.

As the bookshelf.js website says, it uses knex.js query builder. So to sort my collection this is what I have done:

var Accounts = require('../collections/accounts').collection

new Accounts().query(function(qb){
    qb.orderBy('name','DESC'); 
}).fetch({

}).then(function(collection){
    // process results
});

... which works great!

score 12 · Answer 2 · answered Jan 26 '15 at 02:10

You can also simply do

var Accounts = require('../collections/accounts').collection;

new Accounts().query('orderBy', 'columnname', 'asc').fetch({
    withRelated: ['folders']
}).then(function(collection) {
    // process results
});

without getting to the query builder.

score 11 · Answer 3 · answered Mar 20 '15 at 00:04

I know this is an old post, but here is my take on it. BookshelfJS is amazing, but it lacks some simple features. So I have created my own base model, called Closet.

For orderBy, this is what Closet looks like:

var Closet = DB.Model.extend({

    /**
     * Orders the query by column in order
     * @param column
     * @param order
     */
    orderBy: function (column, order) {
        return this.query(function (qb) {
            qb.orderBy(column, order);
        });
    }
});

My other models expend Closet instead of Bookshelf.Model. Then you can directly use orderBy:

new Accounts()
    .orderBy('name', 'DESC')
    .fetch()
    .then(function(collection){
        // process results
    });

can this be done for multiple orderBy if the orderBy fields are not fixed based on API .. ? — Mr X, Nov 14 '16 at 11:28

Sort Bookshelf.js results with .orderBy()

3 Answers3

Linked