Multiple count query in sequelize ORM

Question

I'd like to create query which is able to count number of records for every day in month at once in sequelize.js Not like :

Record.count({ where: { createdAt: { $like: '2015-04-14%' } } }).then(function(c) {
  console.log("2015-04-14 have been created" + c + "records");
});

Record.count({ where: { createdAt: { $like: '2015-04-15%' } } }).then(function(c) {
  console.log("2015-04-15 have been created" + c + "records");
});


Record.count({ where: { createdAt: { $like: '2015-04-16%' } } }).then(function(c) {
  console.log("2015-04-16 have been created" + c + "records");
});

....
....

I wanna make query which will returns number of rows at once, not like ask database for this data in 30 queries. It is possible make it with transactions?

I'll use it for chart purposes, so best output from this is like:

[500, 300, 400, 550....]

Thanks for any help!

Answer 1

For this type of query, you could use the postgresql date_trunc function with grouping:

db.record.findAll({
  attributes: [
    [
      db.sequelize.fn('date_trunc', 
        'day', 
        db.sequelize.col('createdAt')
      ), 
      'dateTrunc'
    ],
    [
      db.sequelize.fn('count', 
        db.sequelize.col('id')
      ), 
      'count'
    ]
  ],
  group: '"dateTrunc"'
}).then(function(rows) {
  console.log(rows);
});

Answer 2

My solution with raw query for PostgreSQL:

db.sequelize
  .query("SELECT count(*), date_trunc('month', \"createdAt\") AS date FROM tables WHERE active = true AND \"createdAt\" BETWEEN '"+moment().startOf('year').format()+"' AND '"+moment().format()+"' GROUP BY date")
  .then(function(results){
     res.json(results[0]);
});

I hope it helps to somebody.