CODEWITHSUNDEEP
node.jsknex.js
Ian Jones
Ian Jones

Reputation: 1509

Knex.js multiple orderBy() columns

Is it possible to do multiple orderBy() columns?

knex
  .select()
  .table('products')
  .orderBy('id', 'asc')

The orderBy() chainable only takes a single column key and a sort value, but how can I order by multiple columns?

Upvotes: 37

Views: 49782

Answers (5)

M-Raw
M-Raw

Reputation: 839

orderBy accepts an array of type:

[
  {column: 'id', order: 'asc'},
  {column: 'name', order: 'desc'},
  {column: 'created_at', order: 'desc'},
]

i have a function that takes a param from the request:

sort=id,name,-created_at

and builds an array that is passed to the queryBuilder

columns is an array with the accepted values of table columns

sort(model, sorts, columns) {

    let confirmed = true;

    sorts = sorts.split(',')
    sorts.forEach((sort: string) => {
        sort      = sort.replace('-', '')
        sort      = sort.replace(' ', '')
        confirmed = columns.includes(sort)
        if (!confirmed) {
            let index = sorts.indexOf(sort)
            sorts.splice(index, 1)
        }
    })

    let sortsArr = [];

    sorts.forEach((sort) => {
        if (sort.startsWith('-')) {
            sort = sort.replace('-', '')
            sortsArr.push({column: model.tableName + '.' + sort, order: 'desc'})
        } else {
            sortsArr.push({column: model.tableName + '.' + sort, order: 'asc'})
        }
    })
    return sortsArr;
}

and then use it like this in the query

const sortsArr = sort(model, sorts, model.columns);

knex('users').orderBy(sortsArr)

Upvotes: 0

Rafael Zeffa
Rafael Zeffa

Reputation: 2414

The Knex orderBy function also receives an array:

knex('users').orderBy(['email', 'age', 'name'])

or

knex('users').orderBy(['email', { column: 'age', order: 'desc' }])

or

knex('users').orderBy([{ column: 'email' }, { column: 'age', order: 'desc' }])

Upvotes: 22

mabulu
mabulu

Reputation: 274

You can use the following solution to solve your problem:

const builder = knex.table('products');

sortArray.forEach(
  ({ field, direction }) => builder.orderBy(field, direction)
);

Upvotes: 4

Ian Jones
Ian Jones

Reputation: 1509

The original answer is technically correct, and useful, but my intention was to find a way to programatically apply the orderBy() function multiple times, here is the actual solution I went with for reference:

var sortArray = [
  {'field': 'title', 'direction': 'asc'}, 
  {'field': 'id', 'direction': 'desc'}
];

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

Knex offers a modify function which allows the queryBuilder to be operated on directly. An array iterator then calls orderBy() multiple times.

Upvotes: 21

Kevin
Kevin

Reputation: 3841

You can call .orderBy multiple times to order by multiple columns:

knex
  .select()
  .table('products')
  .orderBy('name', 'desc')
  .orderBy('id', 'asc')

Upvotes: 63

Related Questions