CODEWITHSUNDEEP
sqlnode.jssequelize.js
Vladimir37
Vladimir37

Reputation: 568

Unique values in Sequelize

I use Sequelize ORM for Node.js. I need to get lines with unique values in certain column. Example table:

id | name | group
-----------------
1  | One  | 2
2  | Two  | 1
3  | Three| 2
4  | Four | 3
5  | Five | 1

Query for column group and result:

id | name | group
-----------------
1  | One  | 2
2  | Two  | 1
4  | Four | 3

Lines One, Two and Four was the first who had unique group values. How to make it in Sequelize?

Upvotes: 0

Views: 2002

Answers (1)

cfogelberg
cfogelberg

Reputation: 1488

A Sequelize raw query is one way of getting out the rows that you want:

/*
  var sql =
    SELECT r.id,
         r.name,
         r.groupnum
    FROM s04.row r
    JOIN
      (SELECT min(id) AS id,
              groupnum
       FROM s04.row
       GROUP BY groupnum) s 
    ON r.id = s.id
*/
return sq.query(sql, { type: sq.QueryTypes.SELECT});

The resulting promise will resolve to a JSON array:

[
  {
    "id": 1,
    "name": "One",
    "groupnum": 2
  }
  ...
]

If you then needed to work with these rows as Instances you can call build on each element of the array:

Model.build({ /* attributes-hash */ }, { isNewRecord: false })

See here for an example. If I find a way of doing this via Sequelize function calls (aggregate, find*, etc) that isn't too hideous I'll also post that here as a separate answer.

Upvotes: 3

Related Questions