Sequelize select distinct rows

Question

Sequelize select distinct rows

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.

node.js
sequelize.js

Answer 1

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

at around line 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: true in your sequelize request

hope it helps -_^

Answer 2

Assuming you want to apply DISTINCT to the following query:

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

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

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

(Tested with Sequelize v2.1.0)

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

Answer 3

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

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

Have fun :)

Answer 4

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

Around line 1167, change

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

to

    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.