How to group by createdAt column's date part?

user_147078 0
Viewed 3592

I'm trying to get some subtotals using Sequelize, and this is how my query looks like.

const getAllCustomerEarnings = async (customerAccountId) => {
  return await customerEarnings.findAll({
    attributes: [
      [Sequelize.fn('SUM', Sequelize.col('amount')), 'amount'],
      [Sequelize.fn('date_trunc', 'day', Sequelize.col('createdAt')), 'createdAt'],
    ],
    where: { 
      [Op.and]: [
        {customerAccountId: customerAccountId},
      ] 
    },
    order: [['createdAt', 'ASC']],
    group: 'createdAt'
  })
}

However, what I get as an output are not subtotals on per-day basis. I actually get each and every record from the table, with time part set to 00:00:000Z

What should I change in order to get subtotals for each day?

sequelize.js node.js grouping date javascript
1 Answers

I think I found the solution myself...

The above quoted method produces the following SQL query

SELECT SUM("amount") AS "amount", date_trunc('day', "createdAt") AS "createdAt"
FROM "CustomerEarnings" AS "CustomerEarning"
WHERE ("CustomerEarning"."customerAccountId" = 5)
GROUP BY "createdAt"
ORDER BY "CustomerEarning"."createdAt" ASC;

The problem here is that although I'm selecting "createdAt" as an alias for truncated value from createdAt column, Sequelize is still referencing createdAt column in the table, and not the alias.

I went around this issue by renaming the alias to "createdOn", like this

const getAllCustomerEarnings = async (customerAccountId) => {
  return await customerEarnings.findAll({
    attributes: [
      [Sequelize.fn('SUM', Sequelize.col('amount')), 'amount'],
      [Sequelize.fn('date_trunc', 'day', Sequelize.col('createdAt')), 'createdOn'],
    ],
    where: { 
      [Op.and]: [
        {customerAccountId: customerAccountId},
      ] 
    },
    order: [[Sequelize.literal('"createdOn"'), 'ASC']],
    group: 'createdOn'
  })
}

Notice that I had to also use

[[Sequelize.literal('"createdOn"'), 'ASC']],

in order clause, instead of just using alias name. That's because Sequelize kept changing capitalization of the alias column in order clause to "createdon"...

Hope this helps someone.

user_1470780
Related