What is the best way to determine total revenue by consolidating data from various tables within an IndexedDB database?

Question

What is the best way to determine total revenue by consolidating data from various tables within an IndexedDB database?

Seeking guidance on building a stock/sales application in JavaScript with Dexie.js. I need assistance in efficiently calculating the Total Sales amount without resorting to overly complicated recursive code that triggers multiple queries for a single product's sales total.

Here is an overview of my schema:

clients: "++id, name, phone",
order: "++id, clientId, date",
order_content: "orderId, productId, qty",
product: "++id, name, mu, mk_cost, sa_cost, prod_cost",
stock: "++id, date, productId, qty, lot"

I maintain different product types in the "Product" table along with their prices and other relevant information. When an order is placed, I record the clientId in the Order table and utilize "order_content" to store the items by linking them with the orderId.

Essentially, I am looking to compute totals for each item and then aggregate those amounts.

An attempt was made to run the following code within a db.product.each() loop, but it seems like there might be a more straightforward approach:

var product1Total = 0;
function calculateTotal(productId, price){
db.order_content
.where("productId")
.equals(productId)
.each(function(item){
product1Total += (price * qty)
})
}

Your insights are greatly appreciated!

javascript indexeddb dexie

Answer 1

Answer №1

If you are aiming to calculate the total price for a specific order using a single query, where prod_cost represents the product cost and you are interested in the total cost for a particular order, you can achieve this by implementing something similar to the following:

function computeOrderTotal (orderId) {
    return db.order_content
        .where('orderId').equals(orderId).toArray()
    .then(orderContents => {
        return Promise.all(
            orderContents.map(oc => db.product.get(oc.productId))
        ).then (products => {
            return orderContents.reduce (
                (total, oc, i) => total + oc.qty * products[i].prod_cost, 0);
        });
    });
}

Alternatively, using async functions:

async function computeOrderTotal (orderId) {
    let orderContents = await db.order_content
        .where('orderId').equals(orderId).toArray();

    let products = await Promise.all(orderContents.map(oc =>
        db.product.get(oc.productId));

    return orderContents.reduce (
        (total, oc, i) => total + oc.qty * products[i].prod_cost, 0);
}

Or utilizing vanilla ES5 javascript:

function computeOrderTotal (orderId) {
    return db.order_content
        .where('orderId').equals(orderId).toArray()
    .then(function (orderContents) {
        return Dexie.Promise.all(
            orderContents.map(function (oc) {
                return db.product.get(oc.productId);
            })
        ).then (function (products) {
            return orderContents.reduce (
                function (total, oc, i) {
                    return total + oc.qty * products[i].prod_cost;
                }, 0);
        });
    });
}

Answer 2

If you are aiming to calculate the total price for a specific order using a single query, where prod_cost represents the product cost and you are interested in the total cost for a particular order, you can achieve this by implementing something similar to the following:

function computeOrderTotal (orderId) {
    return db.order_content
        .where('orderId').equals(orderId).toArray()
    .then(orderContents => {
        return Promise.all(
            orderContents.map(oc => db.product.get(oc.productId))
        ).then (products => {
            return orderContents.reduce (
                (total, oc, i) => total + oc.qty * products[i].prod_cost, 0);
        });
    });
}

Alternatively, using async functions:

async function computeOrderTotal (orderId) {
    let orderContents = await db.order_content
        .where('orderId').equals(orderId).toArray();

    let products = await Promise.all(orderContents.map(oc =>
        db.product.get(oc.productId));

    return orderContents.reduce (
        (total, oc, i) => total + oc.qty * products[i].prod_cost, 0);
}

Or utilizing vanilla ES5 javascript:

function computeOrderTotal (orderId) {
    return db.order_content
        .where('orderId').equals(orderId).toArray()
    .then(function (orderContents) {
        return Dexie.Promise.all(
            orderContents.map(function (oc) {
                return db.product.get(oc.productId);
            })
        ).then (function (products) {
            return orderContents.reduce (
                function (total, oc, i) {
                    return total + oc.qty * products[i].prod_cost;
                }, 0);
        });
    });
}

Answer 3

Answer №2

There's no issue with your query, although it is recommended to wrap it in a function that returns a promise. Achieving this can be done by utilizing the promise returned from Dexie's Collection.each().

function calculateTotalAmount(productId, price) {
    var total = 0;
    return db.order_content
        .where("productID")
        .equals(productId)
        .each(function(item){
            total += (price * item.qty)
        }).then (function () {
            return total;
        });
}

Alternatively, using ES7 syntax:

async function calculateTotalPrice(productId, price) {
    var total = 0;

    await db.order_content
        .where("productID")
        .equals(productId)
        .each (item => total += (price * item.qty));

    return total;
}

Answer 4

There's no issue with your query, although it is recommended to wrap it in a function that returns a promise. Achieving this can be done by utilizing the promise returned from Dexie's Collection.each().

function calculateTotalAmount(productId, price) {
    var total = 0;
    return db.order_content
        .where("productID")
        .equals(productId)
        .each(function(item){
            total += (price * item.qty)
        }).then (function () {
            return total;
        });
}

Alternatively, using ES7 syntax:

async function calculateTotalPrice(productId, price) {
    var total = 0;

    await db.order_content
        .where("productID")
        .equals(productId)
        .each (item => total += (price * item.qty));

    return total;
}

What is the best way to determine total revenue by consolidating data from various tables within an IndexedDB database?

Answer №1

Answer №2

Similar questions

Struggling to access values from your model using EL in JavaScript? Let me provide some guidance

The OTP submission in Phone Email's phone authentication using Node JS did not result in the reception of the token

Why are the variables not reflecting the changes when an event is triggered?

Modify Bootstrap Card Styling Using JavaScript

Numeric keypad causing issues with setting minimum and maximum lengths and displaying submit button

Namespacing is not applied to dynamic modules in Vuex

Executing JavaScript code within a Django application to load a file

Can spreading be used for destructuring?

Obtain the index by clicking on an element within an HTMLCollection

Issues with d3.js transition removal functionality not functioning as expected

Ways to invoke a prop function from the setup method in Vue 3

Tips for How to Put a Delay on Ajax Requests and Display a Progress Bar During Loading

Using the async.waterfall function in an Express application

Could someone please provide clarification on this specific JavaScript syntax? I am unsure about the usage of `const {

Implement Next.js deployment on NGINX server with a 403 forbidden error

Separate a single large table into two smaller tables based on the information found in the third column of every row

Dealing with AngularJS: Issue arises when attempting to inject $modal into a controller nested within a directive

Creating a dynamic directive in AngularJS: Learn how to add or remove it from the DOM based on specific conditions

Refreshing information within a table using Ajax Prototype

Issue with Materialize Sidenav: Not functional on iOS devices including iPhones, functions correctly on all other devices