Knex.js multiple orderBy() columns

Question

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?

I'm not sure there's a special function for that, but you can do a `orderByRaw` and write whatever you need — Vsevolod Goloviznin, Apr 01 '16 at 10:31
@VsevolodGoloviznin I was hoping I wouldn't have to do that, but it's my last resort. — Ian Jones, Apr 01 '16 at 10:32

Kevin · Accepted Answer · 2016-04-03T19:47:37.613

59

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

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

edited Apr 03 '16 at 19:47

answered Apr 03 '16 at 19:34

Kevin

3,771
2
31
40

1

Thanks, but is there any way to apply that programatically? for an unknown number of orderBy fields? – Ian Jones Apr 03 '16 at 22:08
5

Just iterate over the list of columns and call `orderBy` for each. I'll save the basic JS as an exercise for you :) – Kevin Apr 04 '16 at 00:33

Ian Jones · Answer 2 · 2016-04-12T06:56:52.157

20

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.

edited Apr 12 '16 at 06:56

answered Apr 04 '16 at 13:21

Ian Jones

1,369
1
10
15

I think the original answer was working as you intended too. For instance: let query = knex .select() .table('products'); _.each(sortArray, function(sort) { query.orderBy(sort.field, sort.direction); }); – nembleton Jul 12 '17 at 07:49

score 16 · Answer 3 · answered Jun 30 '20 at 12:50

16

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' }])

answered Jun 30 '20 at 12:50

Rafael Zeffa

2,334
22
20

This is more elegant way of doing it than marked answer. – Freddy Mar 09 '23 at 14:41

score 4 · Answer 4 · edited Sep 26 '18 at 22:03

4

You can use the following solution to solve your problem:

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

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

edited Sep 26 '18 at 22:03

Grant Miller

27,532
16
147
165

answered Sep 26 '18 at 16:42

mabulu

274
2
5

score 0 · Answer 5 · answered May 19 '22 at 16:34

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)

Knex.js multiple orderBy() columns

5 Answers5

Linked