node.js 续集在 nodejs 中查找所有排序顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36259532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sequelize findAll sort order in nodejs
提问by PPShein
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.
我正在尝试使用 sequelize 从数据库中输出所有对象列表，如下所示，并希望在我在 where 子句中添加 id 时整理出数据。
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.
正如我发现的，它既不是按 id 也不是按名称排序。请帮助我如何解决它。
回答by James111
In sequelize you can easily add order by clauses.
在 sequelize 中，您可以轻松添加 order by 子句。
exports.getStaticCompanies = function () {
return Company.findAll({
where: {
id: [46128, 2865, 49569, 1488, 45600, 61991, 1418, 61919, 53326, 61680]
},
// Add order conditions here....
order: [
['id', 'DESC'],
['name', 'ASC'],
],
attributes: ['id', 'logo_version', 'logo_content_type', 'name', 'updated_at']
});
};
See how I've added the orderarray of objects?
看看我是如何添加order对象数组的？
order: [
['COLUMN_NAME_EXAMPLE', 'ASC'], // Sorts by COLUMN_NAME_EXAMPLE in ascending order
],
Edit:
编辑：
You might have to order the objects once they've been recieved inside the .then()promise. Checkout this question about ordering an array of objects based on a custom order:
一旦在.then()承诺内收到对象，您可能必须对其进行排序。查看有关根据自定义顺序对对象数组进行排序的问题：
How do I sort an array of objects based on the ordering of another array?
回答by meenal
If you want to sort data either in Ascending or Descending order based on particular column, using sequlize js, use the ordermethod of sequlizeas follows
如果您想将数据按照升序或者降序基于特定的列，使用排序sequlize js，使用order的方法sequlize如下
// Will order the specified column by descending order
order: sequelize.literal('column_name order')
e.g. order: sequelize.literal('timestamp DESC')
回答by Pavlo Razumovskyi
If you are using MySQL, you can use order by FIELD(id, ...)approach:
如果您使用的是MySQL，则可以使用order by FIELD(id, ...)方法：
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.
请记住，它可能会很慢。但是应该比用 JS 手动排序更快。
回答by drs
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:
这将产生一个看起来像这样的 SQL 查询：
[...] ORDER BY ("Company"."id"='46128', "Company"."id"='2865', "Company"."id"='49569', [...])
回答by Alex Moore-Niemi
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.)
我认为这在Sequelize 的 order 子句中是不可能的，因为据我所知，这些子句是适用于列表中每个元素的二元运算。（这也是有道理的，因为它通常是对列表进行排序的工作方式。）
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).
因此，一个 order 子句可以通过递归查询“这 2 个元素中的哪一个更旧？”来对列表进行排序。而您的排序不能简化为二元运算 ( compare_bigger(1,2) => 2)，而只是一个任意序列 ( 2,4,11,2,9,0)。
When I hit this issue with findAll, here was my solution (sub in your returned results for numbers):
当我遇到这个问题时findAll，这是我的解决方案（子在您返回的结果中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.
返回[2, 23, 20, 53, 9]. 我不认为我们可以做出更好的权衡。您可以使用 对订购的 id 进行原地迭代findOne，但是当 1 可以执行时，您将进行 n 次查询。
