node.js Sequelize 选择不同的行

Sequelize select distinct rows

提问by shk

Is there a way to select distinct rows from a table using sequelize.js? I looked through the documentation but the "finder methods" do not specify a way to accomplish this task.

有没有办法使用 sequelize.js 从表中选择不同的行？我查看了文档，但“查找程序​​方法”没有指定完成此任务的方法。

采纳答案by sdepold

It's not possible automatically but if you don't mind creating the sql on your own, you could do this:

这是不可能自动的，但如果你不介意自己创建 sql，你可以这样做：

sequelize.query('sql goes here', null, { raw: plain }).success(function(data){
    console.log(data)
})

Have fun :)

玩得开心 ：）

UPDATE

更新

1. Sequelize now uses theninstead of successas promise function.
2. Sequelize.queryhas been refactored to only use paramters sqland options
3. rawaccepts true/falsebut no plainas value.

1. Sequelize 现在使用then而不是success作为 promise 函数。
2. Sequelize.query已重构为仅使用参数sql和options
3. raw接受true/false但不plain作为价值。

So, according to the new version, the code should look like this:

因此，根据新版本，代码应如下所示：

    sequelize.query('sql goes here', { raw: true }).then(function(data){
        console.log(data);
    });

回答by Pascal Ludwig

Assuming you want to apply DISTINCT to the following query:

假设您要将 DISTINCT 应​​用于以下查询：

Tuple.findAll({attributes: ['key', 'value']});

then this is a (hackish) way to achieve what you want without having to write the whole query yourself:

那么这是一种（hackish）方式来实现你想要的，而不必自己编写整个查询：

Tuple.findAll({attributes: [[Sequelize.literal('DISTINCT `key`'), 'key'], 'value']});

(Tested with Sequelize v2.1.0)

（使用 Sequelize v2.1.0 测试）

Edit 2015-06-08: Still works with Sequelize v3.1.1

编辑 2015-06-08：仍然适用于 Sequelize v3.1.1

回答by Ali Sherafat

You can do the following:

您可以执行以下操作：

myModel.findAll({
  attributes: [[sequelize.fn('DISTINCT', sequelize.col('col_name')), 'alias_name']],
  where:{}
}).then(data => {}).....

taken from issuesand it works.

从问题中提取并且它有效。

回答by Decky

edit your "node_modules/sequelize/lib/dialects/mysql/query-generator.js"

编辑你的“node_modules/sequelize/lib/dialects/mysql/query-generator.js”

at around line 118

在 118 号线附近

change

改变

var query = "SELECT <%= attributes %> FROM <%= table %>"

into

进入

var query = "SELECT " + ((options.distinct)? 'DISTINCT ':'') +"<%= attributes %> FROM <%= table %>",

now you can add an option distinct: truein your sequelize request

现在您可以distinct: true在续集请求中添加一个选项

hope it helps -_^

希望有帮助-_^

回答by svvac

This is somewhat similar to the solution proposed by Pascal Ludwig, but for those landing here looking to get a list of distinct values for a given column, you can do the following:

这有点类似于 Pascal Ludwig 提出的解决方案，但对于那些希望获得给定列的不同值列表的人来说，您可以执行以下操作：

MyModel.aggregate('teh_field', 'DISTINCT', { plain: false }).then(...)
// Resolves to: [ { DISTINCT: value1 }, { DISTINCT: value2 }, ... ]

With that, it's easy to transform it into a standard list:

有了它，很容易将其转换为标准列表：

MyModel.aggregate('teh_field', 'DISTINCT', { plain: false })
    .map(function (row) { return row.DISTINCT })
    .then(function (tehValueList) {
        // tehValueList = [ value1, value2, ... ]
    })
;

回答by Afeesudheen

Model.findAll({Attributes: ['col_name1', 'col_name2'], group: ['col_name1', 'col_name2']});

it's perfectly fine with Sequelize 5.21

Sequelize 5.21 完全没问题

回答by Patrick Chu

As of Sequelize version 1.7, the select query has been moved into lib/dialects/abstract/query-generator.js.

从 Sequelize 1.7 版开始，选择查询已移至 lib/dialects/abstract/query-generator.js。

Around line 1167, change

在 1167 行附近，更改

mainQueryItems.push("SELECT "+mainAttributes.join ....)

mainQueryItems.push("SELECT "+mainAttributes.join ....)

to

到

    mainQueryItems.push('SELECT ');
    if (options.distinct) {
        mainQueryItems.push('DISTINCT ');
    }
    mainQueryItems.push(mainAttributes.join(', ') + ' FROM ' + options.table);

    mainQueryItems.push('SELECT ');
    if (options.distinct) {
        mainQueryItems.push('DISTINCT ');
    }
    mainQueryItems.push(mainAttributes.join(', ') + ' FROM ' + options.table);

By the way, I use Sqlite and Postgres, both of which support "DISTINCT". If you're using a dialect that doesn't support distinct, then obviously this line will cause problems for you, since it will be generated for all the SQL flavors that you're using. I suspect this is why this simple change hasn't made it into the main Sequelize source tree.

顺便说一下，我使用 Sqlite 和 Postgres，它们都支持“DISTINCT”。如果您使用的是不支持 distinct 的方言，那么显然这一行会给您带来问题，因为它将为您使用的所有 SQL 风格生成。我怀疑这就是为什么这个简单的更改没有进入主要 Sequelize 源代码树的原因。