How to query a collection in Firestore and aggregate the documen into `per day`, `per week`?

Question

My goal is to aggregate the document into per day, per week.

document template stored in Firestore.

document = {
  iat: new Date() // Wed Aug 26 2020 HH:MM:SS GMT+... (... Time),
  bill: PropTypes.number,
}

The expected output is

data = {
  labels: [
            08/26/2020,
            08/27/2020,
            ...
          ],
  datasets: [
    ...
    data: [
            bill1 + bill2,
            bill3 + bill4 + bill5,
            bill6,
            bill7 + bill8
          ],
  ],
}

What I've tried. The output is per document.

firebase
  .firestore()
  .collection("tickets")
  .orderBy("exp")
  .get()
  .then((snapshot) => {
    if (!snapshot.empty) {

      setData({
        labels: snapshot.docs.map((doc) => {
          const data = doc.data();
          const exp = data.exp ? new Date(data.exp.seconds * 1000 + data.exp.milliseconds).toString().split(" ").slice(1, 5).join(" ") : null;

          return exp;
        }),
        datasets: [
          {
            data: snapshot.docs.map((doc) => {
              const data = doc.data();
              const bill = data.bill ? data.bill : null;

              return bill;
            }),
          },
        ],
      });

tl:dr

The problem I saw is, it is easier to merge the iat key into per day or per hour. While it is hard to merge the bill key based on the merging of the iat key.

What I can think about now is solving it with nested logic and I imagine it would be cryptic, heads up if you have simpler solution.

I will back in a hour after I found a solution.

Thank you, much appreciated.

Jason Rich Darmawan · Answer 1 · 2020-08-26T17:52:22.597

This is my solution, a cryptic one.

// explaining how dataReduced works.
// acc / accumulator intially does not hold value.
// Therefore, on the first iteration acc[key] = acc[key] returns undefined so we fill it with a value { bill: 0, ...cur }.
// accumulator value is remembered across iteration.
// For comparison, in for loop statement, accumulator is similar to `let i = 0; for (i = 0; condition, execution);`
console.log(
  dataDateEqualized.reduce((acc, { bill, ...cur }) => {
    console.log("acc ", acc, " bill ", bill, " cur ", cur);
    const key = Object.entries(cur).join();

    console.log("acc ", acc, " key ", key, " before ", acc[key]);
    acc[key] = acc[key] || { bill: 0, ...cur };

    console.log("acc ", acc, " after ", acc[key], " bill ", bill);
    return (acc[key].bill += bill); // this is intentional
  }, {})
);

      firebase
        .firestore()
        .collection("tickets")
        .orderBy("exp")
        .get()
        .then((snapshot) => {
          if (!snapshot.empty) {
            const data = snapshot.docs.map((doc) => {
              return doc.data();
            });

            // console.log(data);

            const dataDateEqualized = data.map((data) => {
              const date = new Date(
                new Date(data.exp.seconds * 1000).setHours(0, 0, 0, 0)
              );
              const bill = data.bill;

              return { "exp": date, bill };
            });

            // console.log(dataDateEqualized);

            const dataReduced = Object.values(
              dataDateEqualized.reduce((acc, { bill, ...r }) => {
                const key = Object.entries(r).join("-");
                acc[key] = acc[key] || { ...r, bill: 0 };
                return (acc[key].bill += bill, acc);
              }, {})
            );

            // console.log(dataReduced);

            // console.log(dataReduced.map(data => data.exp.toString().split(" ").slice(1, 4).join(" ")))

            setData({
              labels: dataReduced.map(data => data.exp.toString().split(" ").slice(1, 4).join(" ")),
              datasets: [
                {
                  label: "Revenue Report",
                  fill: false,
                  lineTension: 0.1,
                  backgroundColor: "rgba(75,192,192,0.4)",
                  borderColor: "rgba(75,192,192,1)",
                  borderCapStyle: "butt",
                  borderDash: [],
                  borderDashOffset: 0.0,
                  borderJoinStyle: "miter",
                  pointBorderColor: "rgba(75,192,192,1)",
                  pointBackgroundColor: "#fff",
                  pointBorderWidth: 1,
                  pointHoverRadius: 5,
                  pointHoverBackgroundColor: "rgba(75,192,192,1)",
                  pointHoverBorderColor: "rgba(220,220,220,1)",
                  pointHoverBorderWidth: 2,
                  pointRadius: 1,
                  pointHitRadius: 10,
                  data: dataReduced.map(data => data.bill),
                }
              ]
            })

          } else if (snapshot.empty) setData();
        })
        .catch((error) => setError(error.message));

How to query a collection in Firestore and aggregate the documen into `per day`, `per week`?

1 Answers1