Multiple count request in continuation of ORM

Question

I would like to create a query that can count the number of records for each day of the month right away in the secelize.js file

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 want to make a query that will return several rows at once, and not how to query the database for this data in 30 queries. Can I do this with transactions?

I will use it for chart purposes, so the best conclusion from this:

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

Thanks for any help!

Answer 1

For this type of query, you can 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 raw query solution 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 this helps someone.