【Sequelize5.x】sequelizeでGROUP_CONCAT関数を使ってみた

初めに

自分が実際にGROUP_CONCATを使用したときは2つのテーブルを結合した状態だったので、同じ状況でまとめたいと思います。結合していない状態でも使用できると思いますが、微妙に書き方が変わるので適宜読み替えて実装してください。

実装

usersテーブルとtasksテーブルがあるとします。

models/users.js

module.exports = (sequelize, DataTypes) => {
  const users = sequelize.define('users', {
    name: DataTypes.STRING
  }, {});
  users.associate = function(models) {
    users.hasMany(models.tasks, {
      foreignKey: 'userId'
    });
  };
  return users;
};

models/tasks.js

module.exports = (sequelize, DataTypes) => {
  const tasks = sequelize.define('tasks', {
    task: DataTypes.STRING,
    userId: DataTypes.INTEGER
  }, {});
  tasks.associate = function(models) {
    tasks.belongsTo(models.users, {
      foreignKey: 'userId'
    });
  };
  return tasks;
};

2つのテーブルを結合し、以下のようなクエリを発行するとします。

MySQL

SELECT users.id, name, GROUP_CONCAT(task) AS task_list
FROM users
LEFT JOIN tasks
ON users.id = tasks.user_id 
GROUP BY users.id

sequelizeでは、sumやcountを使用する時と同様にsequelize.fn()を使います。

sequelize

users.findAll({
  raw: true,
  include: [{
    model: tasks,
    attributes: [
      [sequelize.fn('GROUP_CONCAT', sequelize.literal('task')), 'task_list'],
    ],
  }],
  attricutes: [
    'users.id',
    'name'
  ],
  group: ['users.id'],
});

取得してきた結果は以下のようになります。

id	name	task_list
1	taro	牛乳を買う、掃除する、風呂に入る、勉強する
2	jiro	服を買う、掃除する、洗濯する
3	saburo	掃除する、風呂に入る、洗濯する
4	siro	服を買う、勉強する
5	goro	牛乳を買う
6	rokuro	服を買う、掃除する、風呂に入る、洗濯する

自分の実装時には、task_listがカンマ区切りになることや、並び順の整合性が取れないことが不都合だったので、SEPARATORやORDER BYを付与してみたいと思います。

MySQL

SELECT users.id, name, GROUP_CONCAT(task ORDER BY task.id ASC SEPARATOR '@') AS task_list
FROM users
LEFT JOIN tasks
ON users.id = tasks.user_id 
GROUP BY users.id

sequelize.fn()にSEPARATORやORDER BYをそのまま記述することができます。sequelize.literal()内が長くなってしまうので、定数化してしまうのもいいと思います。

sequelize

users.findAll({
  raw: true,
  include: [{
    model: tasks,
    attributes: [
      [sequelize.fn('GROUP_CONCAT',
        sequelize.literal('task ORDER BY tasks.id ASC SEPARATOR "@"')),
      'task_list'],
    ],
  }],
  attricutes: [
    'users.id',
    'name'
  ],
  group: ['users.id'],
});

取得してきた結果は以下のようになります。

id	name	task_list
1	taro	牛乳を買う@掃除する@風呂に入る@勉強する
2	jiro	服を買う@掃除する@洗濯する
3	saburo	掃除する@風呂に入る@洗濯する
4	siro	服を買う@勉強する
5	goro	牛乳を買う
6	rokuro	服を買う@掃除する@風呂に入る@洗濯する

まとめ

GROUP_CONCAT関数が使えるか、sequelizeの公式にも載っていなかった（載っていたらすみません。。）のでわかりませんでしたが、sequelize.fn()でMySQLのクエリは大体実行できそうですね。まだ使用したことがないクエリを発行するときはとりあえずsequelize.fn()で試してみようと思います。