SequelizeでGROUP BYしてみる

sequelize
node

事前データ

nameカラムにランダムにname-0からname-9を入れたデータを合計100個作ります。 nameにGROUP BYをしてそれぞれの名前がいくつあるかを調べてみます。

const db = require('./models/index');

for(let i=0; i<100; i++){
  db.User.create({
    name: `name-${Math.floor(Math.random() * 10)}`
  })
}

データ

mysql> select * FROM users LIMIT 10;
+----+--------+---------------------+---------------------+
| id | name   | createdAt           | updatedAt           |
+----+--------+---------------------+---------------------+
|  1 | name-0 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  2 | name-4 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  3 | name-8 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  4 | name-8 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  5 | name-2 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  6 | name-9 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  7 | name-2 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  8 | name-2 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
|  9 | name-4 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
| 10 | name-3 | 2018-05-08 06:22:23 | 2018-05-08 06:22:23 |
+----+--------+---------------------+---------------------+
10 rows in set (0.00 sec)

GROUP BYでCOUNTしてみる

groupにGROUP BYするカラムを指定して、集約するカラムをattributesに指定します。

const db = require('./models/index');

db.User.findAll({
  attributes: [
    'name',
    [db.sequelize.fn('count', db.sequelize.col('name')), 'countName']
  ],
  group: 'name'
}).then((users)=>{
  const userList = users.map((user)=>{
    return user.dataValues;
  });
  console.log(userList);
  db.sequelize.close();
})

結果

SQL

Executing (default): SELECT `name`, count(`name`) AS `countName` FROM `Users` AS `User` GROUP BY `name`;

出力

[ { name: 'name-0', countName: 12 },
  { name: 'name-1', countName: 6 },
  { name: 'name-2', countName: 16 },
  { name: 'name-3', countName: 13 },
  { name: 'name-4', countName: 10 },
  { name: 'name-5', countName: 8 },
  { name: 'name-6', countName: 13 },
  { name: 'name-7', countName: 7 },
  { name: 'name-8', countName: 8 },
  { name: 'name-9', countName: 7 } ]

関連記事