javascript - 动态添加属性到sequelize.js查询

Question

我有一个包含多个属性的查询，如下所示。

const queryPvCompletedByMonth = {
    attributes: [
      [
        Sequelize.literal(
          `COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '${getFirstAndLastDayInMonth(0).startDate}' AND '${
            getFirstAndLastDayInMonth(0).endDate
          }' THEN 1 ELSE NULL END)`
        ),
        'Jan',
      ],
      [
        Sequelize.literal(
          `COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '${getFirstAndLastDayInMonth(1).startDate}' AND '${
            getFirstAndLastDayInMonth(1).endDate
          }' THEN 1 ELSE NULL END)`
        ),
        'Feb',
      ],
      [
        Sequelize.literal(
          `COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '${getFirstAndLastDayInMonth(2).startDate}' AND '${
            getFirstAndLastDayInMonth(2).endDate
          }' THEN 1 ELSE NULL END)`
        ),
        'Mar',
      ],
    ],
    where: {
      Field2: 2,
    },
    raw: false,
  };

接下来的几个月都是如此。这有效，我从查询中得到了预期的结果。

sequelize 生成的查询如下所示。

SELECT COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '2018-01-01' AND '2018-01-31' THEN 1 ELSE NULL END) AS [Jan], COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '2018-02-01' AND '2018-02-28' THEN 1 ELSE NULL END) AS [Feb], COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '2018-03-01' AND '2018-03-31' THEN 1 ELSE NULL END) AS [Mar] FROM [dbo].[Table1] AS [table1] WHERE [table1].[field2] = 2;

我不想动态插入 12 个硬编码属性。所以我把所有月份都放在这样的数组中。

const months = [
  { index: 0, name: 'Jan' },
  { index: 1, name: 'Feb' },
  { index: 2, name: 'Mar' },
  { index: 3, name: 'Apr' },
  { index: 4, name: 'May' },
  { index: 5, name: 'Jun' },
  { index: 6, name: 'Jul' },
  { index: 7, name: 'Aug' },
  { index: 8, name: 'Sep' },
  { index: 9, name: 'Oct' },
  { index: 10, name: 'Nov' },
  { index: 11, name: 'Dec' },
];

并尝试映射列表以返回这样的属性。

const queryPvCompletedByMonth = {
    attributes: [
      months.map(m => [
        Sequelize.literal(
          `COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '${
            getFirstAndLastDayInMonth(m.index).startDate
          }' AND '${getFirstAndLastDayInMonth(m.index).endDate}' THEN 1 ELSE NULL END)`
        ),
        m.name,
      ]),
    ],
    where: {
      AOTyp: 2,
    },
    raw: false,
  };

这给了我一个来自sequelize的错误

[[{\"val\":\"COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '2018-01-01' AND '2018-01-31' THEN 1 ELSE NULL END)\"},\"Jan\"],[{\"val\":\"COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '2018-02-01' AND '2018-02-28' THEN 1 ELSE NULL END)\"},\"Feb\"],[{\"val\":\"COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '2018-03-01' AND '2018-03-31' THEN 1 ELSE NULL END)\"},\"Mar\"]] is not a valid attribute definition. Please use the following format: ['attribute definition', 'alias']

因此，它创建了正确的查询，但带有大量 axtra 符号。

所以我的问题是，有没有办法实现这一目标？

Answer 1

我通过将属性声明为查询外部的空数组，然后将每个属性插入其中来使其工作。

const attributesPvCompletedByMonth = [];

map 功能:

const generateAttributesForPvCompletedMyMonths = () => {
  months.map(m => {
    const attribute = [
      Sequelize.literal(
        `COUNT(CASE WHEN CONVERT(date,[Field1]) BETWEEN '${
          getFirstAndLastDayInMonth(m.index).startDate
        }' AND '${getFirstAndLastDayInMonth(m.index).endDate}' THEN 1 ELSE NULL END)`
      ),
      m.name,
    ];
    return attributesPvCompletedByMonth.push(attribute);
  });
};

查询

const queryPvCompletedByMonth = {
  attributes: attributesPvCompletedByMonth,
  where: {
    Field2: 2,
  },
  raw: true,
};