CODEWITHSUNDEEP
javascriptnode.jssequelize.js
stewart715
stewart715

Reputation: 5647

node sequelize sort by multiple columns

Coming from a Ruby/Rails background, I'm used to this type of syntax:

Model.all.order('col1 + col2 + col3')

So, given that col1, col2 and col3 are integers, for example, it would sort the results by the sum of those 3 columns.

Is there a similar way to sort like this using sequelize?

Upvotes: 11

Views: 17279

Answers (3)

Laurelg
Laurelg

Reputation: 11

Had to sort by a col1+col2 in my model's defaultScope, the SUM solution provided as answer returned a sequelize error function sum(numeric, numeric) does not exists (or something similar).

My current workaround :

order: [[ 'col1', sequelize.literal('+'), 'col2', 'DESC']]

which will print out

ORDER BY "tableName"."col1"+"col2" DESC

just add more interspaced columns and sequelize.literal('+') for more columns.

If using includes/joins and you need all columns to be called with the table name... I tried sequelize.col('col2') but the table name wasn't added. I guess a combination like 'tableName', sequelize.literal('.'), 'columnName' should work, but you need to know your tableName/alias. And I couldn't access my model since it was being defined in my use case. The {model: 'model', as: 'alias'} from the documentation failed too.

Upvotes: 0

Faris Rayhan
Faris Rayhan

Reputation: 4636

order: [
   ['created_at', 'desc'],
   ['id', 'desc']
]

or

order: [
   [sequelize.literal('created_at, id'), 'desc']
]

Upvotes: 13

Jan Aagaard Meier
Jan Aagaard Meier

Reputation: 28788

With the latest master (not sure if the change has been pushed to npm yet), you can do the following:

Model.findAll({ order: [[{ raw: 'col1 + col2 + col3 DESC' }]]});

Resulting in

ORDER BY col1 + col2 + col3 DESC

Or you can do:

Model.findAll({ order: [[sequelize.fn('SUM', sequelize.col('col1'), sequelize.col('col2'), sequelize.col('col3')), 'DESC']]});

Resulting in

ORDER BY SUM(`col1`, `col2`, `col3`) DESC

I would recommend the second version, which will properly escape the column names. For more info see http://sequelizejs.com/documentation#models-finders-limit---offset---order---group

Upvotes: 10

Related Questions