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!

Project

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

Rhynchocephalian

Thanks a lot, I already created it with raw query. Basically your solution is same. – Project

Are you Dj really? I have price columnt in this rows.. Do you know how can I sum all these prices in this case for all records in particular day? – Project

Use the sum function instead of count. See this attribute definition: [ db.sequelize.fn('sum', db.sequelize.col('price') ), 'priceSum' ] – Rhynchocephalian

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.

Project