如何在Sequelize中自定义GROUP_CONCAT的分隔符？

原查询代码 #

groupAccess.findAll({
  attributes: [
    'id',
    'group_name',
    'description',
    [sequelize.fn('GROUP_CONCAT', sequelize.col('module_name')), 'module_name']
  ],
  group: ['group_name']
})

原执行结果 #

数据库查询结果 #

id  |  group_name  |  description  |  module_name
----+--------------+---------------+---------------------------
1   |  A           |  desc A       |  mod_1,mod_2,mod_3
4   |  B           |  desc B       |  mod_4,mod_5,mod_6,mod_7
8   |  C           |  desc C       |  mod_8,mod_9,mod_10
11  |  D           |  desc D       |  mod_11,mod_12

Sequelize返回结果 #

[
  {
    id: "1",
    group_name: "A",
    description: "desc A",
    module_name: "mod_1,mod_2,mod_3"
  },
  {
    id: "4",
    group_name: "B",
    description: "desc B",
    module_name: "mod_4,mod_5,mod_6,mod_7"
  },
  {
    id: "8",
    group_name: "C",
    description: "desc C",
    module_name: "mod_8,mod_9,mod_10"
  },
  {
    id: "11",
    group_name: "D",
    description: "desc D",
    module_name: "mod_11,mod_12"
  },
]

问题 #

如何在Sequelize中将GROUP_CONCAT的逗号分隔符替换为其他符号（例如->符号）？

解决方案 #

MySQL的GROUP_CONCAT函数支持通过SEPARATOR参数指定自定义分隔符，在Sequelize中只需给sequelize.fn添加对应的参数即可。

修改后的查询代码 #

groupAccess.findAll({
  attributes: [
    'id',
    'group_name',
    'description',
    [
      sequelize.fn(
        'GROUP_CONCAT', 
        sequelize.col('module_name'), 
        ' SEPARATOR ->'  // 指定自定义分隔符，注意SEPARATOR与符号间需加空格
      ), 
      'module_name'
    ]
  ],
  group: ['group_name']
})

预期执行结果 #

数据库查询结果 #

id  |  group_name  |  description  |  module_name
----+--------------+---------------+---------------------------
1   |  A           |  desc A       |  mod_1->mod_2->mod_3
4   |  B           |  desc B       |  mod_4->mod_5->mod_6->mod_7
8   |  C           |  desc C       |  mod_8->mod_9->mod_10
11  |  D           |  desc D       |  mod_11->mod_12

Sequelize返回结果 #

[
  {
    id: "1",
    group_name: "A",
    description: "desc A",
    module_name: "mod_1->mod_2->mod_3"
  },
  {
    id: "4",
    group_name: "B",
    description: "desc B",
    module_name: "mod_4->mod_5->mod_6->mod_7"
  },
  {
    id: "8",
    group_name: "C",
    description: "desc C",
    module_name: "mod_8->mod_9->mod_10"
  },
  {
    id: "11",
    group_name: "D",
    description: "desc D",
    module_name: "mod_11->mod_12"
  },
]

内容的提问来源于stack exchange，提问作者masadamsahid