Multiple orderBy() column in bookshelfjs

Question

How to implement multiple orderBy in bookshelfJs

I can add as many orderBy to model but in API there can be any sort options like example.com/users?sort=-name,status and it need not to be hard coded.

The below answer seems legit for the requirement

Knex.js multiple orderBy() columns

but How to implement multiple orderBy in Bookshelf?

models/Users.js

var Bookshelf = require('../../dbConfig').bookshelf;

var User = Bookshelf.Model.extend({
    tableName: 'user_table'
});
var Users = Bookshelf.Collection.extend({
    model: User
});

module.exports = {
    User: User,
    Users: Users
};

services.js

var Model = require('./../models/Users');
var express = require('express');

var listAllContentProviders = function (query_params, callback) {
Model.Users
        .forge()
        .orderBy("name")
        .orderBy("-status")
        .fetch()
        .then(function (collection) {
            return callback(null, collection);
        })
        .catch(function (err) {
            return callback(err, null);
        });  
};

score 1 · Answer 1 · edited May 23 '17 at 12:24

get querystring from url,

   var queryData = url.parse(request.url, true).query;
    var sortArray = [];
    if (queryData.sort) {
        sortArray.push({field: queryData.sort , 'direction': 'asc'};
   }

now sortArray have all sort fields,

Now use the answer you added in question (Knex.js multiple orderBy() columns) to complete your requirement

knex
  .select()
  .table('products')
  .modify(function(queryBuilder) {
    _.each(sortArray, function(sort) {
      queryBuilder.orderBy(sort.field, sort.direction);
    });
  })

Not tested:

 Model.Users
    .forge()
    .modify(function(queryBuilder) {
         _.each(sortArray, function(sort) {
            queryBuilder.orderBy(sort.field, sort.direction);
         })
    })
    .fetch()
    .then(function (collection) {
        return callback(null, collection);
    })
    .catch(function (err) {
        return callback(err, null);
    });

New modified:

Try something like this,

    queryBuilder = Model.Users
    .forge()
    .fetch();
    sortArray.forEach( function(sort) {
        queryBuilder.orderBy(sort.field, sort.direction);
    });
    queryBuilder.then(function (collection) {
        return callback(null, collection);
    })
    .catch(function (err) {
        return callback(err, null);
    });

If you got any issue with resolving promise, then modify it like,

    queryBuilder = Model.Users
    .forge();
    queryBuilder.then(function(){
        sortArray.forEach( function(sort) {
            queryBuilder.orderBy(sort.field, sort.direction);
        })
    }).fetch().then(function (collection) {
        return callback(null, collection);
    })
    .catch(function (err) {
        return callback(err, null);
    });

Yes .. just have to append the knex query as function and loop for whetever fields we wants sorting ...`query(function (qb) { for (var i in sortArray) { qb.orderBy(sortArray[i].field, sortArray[i].direction); } })` — Mr X, Nov 15 '16 at 08:10

score 0 · Accepted Answer · answered Nov 15 '16 at 08:14

var sortArray = [];
var expectedSortFields = ["id", "status"]; // where we want the sorting remaining parameter will be ignored.

for (var x in query_params) {
        if (x === 'sort') {
            var sortFields = query_params[x].split(',');
            for (var y in sortFields) {
                if (expectedSortFields.includes(sortFields[y])) {
                    sortArray[y] = {
                        'field': sortFields[y],
                        'direction': 'desc' //just made it desc for test, this can also be checked basedon the sign given in API '-' for desc and by default asc
                    }
                }
            }
        }

    }

Model. Users
        .forge()
        .query(function (qb) {
            for (var i in sortArray) {
                qb.orderBy(sortArray[i].field, sortArray[i].direction);
            }
        })
        .then(function (collection) {
            return callback(null, collection);
        })
        .catch(function (err) {
            return callback(err, null);
        });

Multiple orderBy() column in bookshelfjs

2 Answers2