sequelize.js - Sequelize 按桶数组创建组

Question

假设我在用户表中有 5 列

   name            email              state          isOnline            createdAt
| Name1 |   | name1@gmail.com |     | Kerala    |   | true  |    | 2022-08-17 10:05:32.755215+05:30 |
| Name2 |   | name2@gmail.com |     | TamilNadu |   | false |    | 2022-08-16 10:05:32.755215+05:30 |
| Name3 |   | name3@gmail.com |     | Karnataka |   | false |    | 2022-08-16 10:05:32.755215+05:30 |
| Name4 |   | name4@gmail.com |     | Karnataka |   | false |    | 2022-08-16 10:05:32.755215+05:30 |
| Name5 |   | name5@gmail.com |     | Karnataka |   | false |    | 2022-08-16 10:05:32.755215+05:30 |
| Name6 |   | name6@gmail.com |     | TamilNadu |   | false |    | 2022-08-16 10:05:32.755215+05:30 |
| Name7 |   | name7@gmail.com |     | TamilNadu |   | false |    | 2022-08-16 10:05:32.755215+05:30 |

我需要使用类似于 mongodb $bucket 的 sequalize ORM 将这些结果组合到一组桶数组中。所以我会有这样的东西:

{
    states: [
        {
            name: 'TamilNadu',
            count: 3
        },
        {
            name: 'Karnataka',
            count: 3
        },
        {
            name: 'Kerala',
            count: 1
        }
    ],
    isOnline: 1,
    isOffline: 6,
    createdAt: [
        {
            name: 'withinADay',
            count: 1
        },
        {
            name: 'withinAWeek',
            count: 7
        },
        {
            name: 'withinAMonth',
            count: 7
        }
    ]
}

我不知道如何实现这一目标。指导我创建预期的查询，以便我在应用程序中有一个过滤器选项。

Answer 1

在SQL中，有针对这个问题的窗口函数。

Sequilize 似乎不支持窗口函数:Issue Link

因此可以通过子查询或发送多个查询并组合它们来解决问题

这是我的 node.js 解决方案。它给出了预期的结果，可以进一步清理和优化:

// States
const states = await User.findAll({
  attributes: [
    ["state", "name"],
    [sequelize.fn('COUNT', sequelize.col('state')), 'count']
  ],
  group: "state"
})

// Online status
const onlineCounts = await User.findAndCountAll({
  attributes: ["isOnline"],
  group: "isOnline"
})

const [isOnline, isOffline] = [onlineCounts.count[0].count, onlineCounts.count[1].count]

// Custom logic for createdAt calculation
const createdAtRanges = await User.findAll({
  attributes: [
    [sequelize.fn('age', sequelize.col('createdAt')), "date"]
  ]
})

let createdAt = [
  { name: 'withinADay', count: 0 },
  { name: 'withinAWeek', count: 0 },
  { name: 'withinAMonth', count: 0 }
]

createdAtRanges.forEach(range => {
  let days = range.dataValues.date.days ?? 0
  if (days <= 1) createdAt[0].count++
  if (days <= 7) createdAt[1].count++
  if (days <= 30) createdAt[2].count++
})

res.json({ states, isOnline, isOffline, createdAt })