I'm trying to output all object list from database with sequelize as follow and want to get data are sorted out as I added id in where clause.
exports.getStaticCompanies = function () {
return Company.findAll({
where: {
id: [46128, 2865, 49569, 1488, 45600, 61991, 1418, 61919, 53326, 61680]
},
attributes: ['id', 'logo_version', 'logo_content_type', 'name', 'updated_at']
});
};
But the problem is after rendering, all data are sorted out as follow.
46128, 53326, 2865, 1488, 45600, 61680, 49569, 1418, ....
As I found, it's neither sorted by id nor name. Please help me how to solve it.
This question is related to
node.js
express
sequelize.js
If you want to sort data either in Ascending or Descending order based on particular column, using sequlize js
, use the order
method of sequlize
as follows
// Will order the specified column by descending order
order: sequelize.literal('column_name order')
e.g. order: sequelize.literal('timestamp DESC')
I don't think this is possible in Sequelize's order clause, because as far as I can tell, those clauses are meant to be binary operations applicable to every element in your list. (This makes sense, too, as it's generally how sorting a list works.)
So, an order clause can do something like order a list by recursing over it asking "which of these 2 elements is older?" Whereas your ordering is not reducible to a binary operation (compare_bigger(1,2) => 2
) but is just an arbitrary sequence (2,4,11,2,9,0
).
When I hit this issue with findAll
, here was my solution (sub in your returned results for numbers
):
var numbers = [2, 20, 23, 9, 53];
var orderIWant = [2, 23, 20, 53, 9];
orderIWant.map(x => { return numbers.find(y => { return y === x })});
Which returns [2, 23, 20, 53, 9]
. I don't think there's a better tradeoff we can make. You could iterate in place over your ordered ids with findOne
, but then you're doing n queries when 1 will do.
If you are using MySQL, you can use order by FIELD(id, ...)
approach:
Company.findAll({
where: {id : {$in : companyIds}},
order: sequelize.literal("FIELD(company.id,"+companyIds.join(',')+")")
})
Keep in mind, it might be slow. But should be faster, than manual sorting with JS.
You can accomplish this in a very back-handed way with the following code:
exports.getStaticCompanies = function () {
var ids = [46128, 2865, 49569, 1488, 45600, 61991, 1418, 61919, 53326, 61680]
return Company.findAll({
where: {
id: ids
},
attributes: ['id', 'logo_version', 'logo_content_type', 'name', 'updated_at'],
order: sequelize.literal('(' + ids.map(function(id) {
return '"Company"."id" = \'' + id + '\'');
}).join(', ') + ') DESC')
});
};
This is somewhat limited because it's got very bad performance characteristics past a few dozen records, but it's acceptable at the scale you're using.
This will produce a SQL query that looks something like this:
[...] ORDER BY ("Company"."id"='46128', "Company"."id"='2865', "Company"."id"='49569', [...])
Source: Stackoverflow.com