CODEWITHSUNDEEP
javascriptnode.jssequelize.js
Zayco
Zayco

Reputation: 327

Dynamically add attributes to sequelize.js query

I have a query with multiple attributes that look like this. 

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,
  };

And so on for all the months. This works, and I am getting the expected result from the query.

The query generated by sequelize looks like this.

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;

Instead of having 12 hardcoded attributes I would like to insert them dynamically. So I put all the months in an array like this. 

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' },
];

And tried to map through the list to return the attributes like this.

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,
  };

This gives me an error from 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']

So it kind of creates the right query but with alot of axtra symbols.

So my question is, is there a way to achieve this?

Upvotes: 3

Views: 6260

Answers (1)

Zayco
Zayco

Reputation: 327

I got it working by declaring attributes as an empty array outside of the query and then pushing each attribute into it.

const attributesPvCompletedByMonth = [];

The map function:

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);
  });
};

The query

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

Upvotes: 1

Related Questions