Multiple db queries to return as json object. a cleaner approach?

Question

So... Here is the code I'm working with. I was wondering if there's a cleaner way to do this. Because the code will just pile up and would look like a messy block of code. This is mariah db + express.

app.get

("/ordersInfo", function (req, res) {
  connection.query("SELECT SUM(`subtotal`) as data FROM `orders` WHERE created_at BETWEEN '" + moment().startOf("day").format() + "' AND '" + moment().endOf("day").format() + "'", (err, dailyTotalRevenue) => {
    connection.query("SELECT SUM(`subtotal`) as data FROM `orders` WHERE created_at BETWEEN '" + moment().startOf("week").format() + "' AND '" + moment().endOf("week").format() + "'", (err, weeklyTotalRevenue) => {
      connection.query("SELECT SUM(`subtotal`) as data FROM `orders` WHERE created_at BETWEEN '" + moment().startOf("month").format() + "' AND '" + moment().endOf("month").format() + "'", (err, monthlyTotalRevenue) => {
        connection.query("SELECT COUNT(*) as data FROM `orders` WHERE created_at BETWEEN '" + moment().startOf("day").format() + "' AND '" + moment().endOf("day").format() + "'", (err, dailyTotalOrderCount) => {
          connection.query("SELECT COUNT(*) as data FROM `orders` WHERE created_at BETWEEN '" + moment().startOf("week").format() + "' AND '" + moment().endOf("week").format() + "'", (err, weeklyTotalOrderCount) => {
            connection.query("SELECT COUNT(*) as data FROM `orders` WHERE created_at BETWEEN '" + moment().startOf("month").format() + "' AND '" + moment().endOf("month").format() + "'", (err, monthlyTotalOrderCount) => {
              connection.query("SELECT * FROM `orders` WHERE created_at BETWEEN '" + moment().startOf("month").format() + "' AND '" + moment().endOf("month").format() + "'", (err, month) => {
                connection.query("SELECT COUNT(*) as data FROM `orders` WHERE status = 'pending'", (err, totalPending) => {
                  connection.query("SELECT COUNT(*) as data FROM `orders` WHERE status = 'ready_to_ship'", (err, totalReadyToShip) => {
                    if (err) {
                      console.log(err);
                      res.json({ error: true });
                    } else {
                      return res.json({
                        daily: {
                          totalRevenue: (dailyTotalRevenue[0].data) ? dailyTotalRevenue[0].data : 0,
                          totalOrderCount: (dailyTotalOrderCount[0].data) ? dailyTotalOrderCount[0].data : 0,
                        },
                        weekly: {
                          totalRevenue: (weeklyTotalRevenue[0].data) ? weeklyTotalRevenue[0].data : 0,
                          totalOrderCount: (weeklyTotalOrderCount[0].data) ? weeklyTotalOrderCount[0].data : 0,
                        },
                        monthly: {
                          totalRevenue: (monthlyTotalRevenue[0].data) ? monthlyTotalRevenue[0].data : 0,
                          totalOrderCount: (monthlyTotalOrderCount[0].data) ? monthlyTotalOrderCount[0].data : 0,
                        },
                        total: {
                          totalPending: totalPending[0].data,
                          totalReadyToShip: totalReadyToShip[0].data,
                        }
                      });
                    }
                  });
                });
              });
            });
          });
        });
      });
    });
  });
});

The result looks like this:

It gets the job done, but I there a bunch more info I'm going to need to retrieve from that one table from my DB. Is there a cleaner and/or more efficient approach to this?

I don't know much about maria/MySQL, but in Postgresql I'd do that in a single query, where each `SUM()` would be a `CASE` expression in the `SELECT` clause. That way you'd get one row back with all the values you need. — Pointy, Feb 14 '21 at 13:19

MaartenDev · Accepted Answer · 2021-02-14T13:45:33.603

The separate query's can be refactored into one:

select sum(case when DATE(created_at) = CURDATE() then subtotal end) as dailyTotalRevenue,
       sum(case when YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1) then subtotal end) as weeklyTotalRevenue,
       count(case when status = 'pending' then 1 end) as pendingCount,
       count(case when status = 'ready_to_ship' then 1 end) as readyToShipCount
from orders

The following table data:

results in(today is 2021-02-14):

more information: Is it possible to specify condition in Count()?

Keep in mind that an extra argument(1) has passed to YEARWEEK, to mark Monday as the starting date of the week.

Iman Tahriri · Answer 2 · 2021-02-14T13:26:44.330

You can use a promise chain to write it cleaner and more readable. A sudo code for that would look like this:

A general function to query the db:

function QueryDB(queryString) {
  return new Promise((resolve, reject) => {
    connection.query(queryString, (err, data) => {
      if (err) reject(err.message);
      resolve(data);
    });
  });
}

Then you can chain it like this:

QueryDB(qs1).then(data => QueryDB(qs2)).then(data => QueryDB(qs3))./*more chaining*/.catch(err => console.log(err))

Multiple db queries to return as json object. a cleaner approach?

2 Answers2