Sequelize: Selecting distinct field values from multiple columns

user_7396516 0
Viewed 6109

I'm using sequelize and mysql2 and need to select all the distinct values from multiple columns. For example I've a 'Title' and a 'Location' column.

At the moment this:

Job.findAll({
    attributes: [
        [Sequelize.fn('DISTINCT', Sequelize.col('title')), 'title']
    ]
})

works, returning all the job titles that are distinct. But if I add a second line for the location column I get a syntax error

Job.findAll({
    attributes: [
        [Sequelize.fn('DISTINCT', Sequelize.col('title')), 'title'],
        [Sequelize.fn('DISTINCT', Sequelize.col('location')), 'location']
    ]
})

It feels like it should work, from what I've read in the docs at least - but I'm not exactly experienced or confident with sequelize, so any help would be appreciated.

Thanks

sequelize.js mysql javascript
1 Answers

I haven't yet found a way to do this less-hackily, but you can exploit the fact that DISTINCT isn't really a function, but a statement and always operates on the entirety of the selected column set:

Job.findAll({
    attributes: [
        [Sequelize.fn('DISTINCT', Sequelize.col('title')), 'title'],
        'location'
    ]
})

would generate SQL similar to

SELECT DISTINCT(`title`) AS `title`, `location` FROM `Jobs`;

but since DISTINCT is not a function, this is really the same as

SELECT DISTINCT (`title`) AS `title`, `location` FROM `Jobs`;

which does do what you want because parenthesis around column names are optional, that query up there would be the same as

SELECT DISTINCT `title`, `location` FROM `Jobs`;

Source: https://dzone.com/articles/sql-distinct-is-not-a-function (the article also talks about the DISTINCT ON PostgreSQL extension, if someone happens to need that too)

Please note that this feels very hacky and sort of fragile and could/might break if Sequelize ever decides to change the way functions are applied when generating the SQL, but it works in the version of Sequelize I use (5.22.3).

user_17355600
Related