Find & sort is not always returning the most recent record

Working with Data
, ,
1

Hello,
I am using mongodb atlas in my project.
I have a scenario where I always must return the newest document in my query. But I noticed in some cases the newest document is not returned due to which I am getting wrong account balances.
Below is the function I am using.

const getStoreReceivableBalance = async (storeId, companyId) => {
    try {

        const storeBalance = await mycollection.find({ storeId, companyId }).sort({ 'createdAt': -1 }).limit(1);
        if (storeBalance.length > 0) {
            return storeBalance[0].balance;
        } else {
            return 0
        }

    } catch (error) {
        console.log('error', error);
    }
}
// calculate new balance of store
let currentStoreReceivableBalance = await transactionServices.getStoreReceivableBalance(storeId, companyId);
currentStoreReceivableBalance = currentStoreReceivableBalance + totalPrice;

With the above flow there are cases where the new balance is wrong. I am showing an output where I got a wrong balance.

/* 4 createdAt:7/2/2022, 8:37:44 PM*/
{
	"_id" : ObjectId("62c02e08c5779f54ce56e42f"),
	"paid" : 232820000,
	"received" : 0,
	"total" : Double("7479949874"),
	"balance" : Double("7479949874"),
	"isDeleted" : false,
	"reference" : "1574",
	"storeId" : ObjectId("61cf4afdea853a367998f6e4"),
	"companyId" : ObjectId("60e5c27b5dd888474611b6a4"),
	"createdBy" : ObjectId("61d2cb4f3e5a0f1e3f01c42d"),
	"description" : "Order",
	"prefix" : "FC",
	"createdAt" : ISODate("2022-07-02T20:37:44.665+09:00")
},

/* 5 createdAt:7/2/2022, 3:08:47 AM*/
{
	"_id" : ObjectId("62bf382fc5779f83bd56dc6f"),
	"paid" : 0,
	"received" : 114957600,
	"total" : Double("7132172274"),
	"balance" : Double("7132172274"),
	"isDeleted" : false,
	"reference" : "1571",
	"storeId" : ObjectId("61cf4afdea853a367998f6e4"),
	"companyId" : ObjectId("60e5c27b5dd888474611b6a4"),
	"createdBy" : ObjectId("61b86f6e3d35436056f97522"),
	"recordType" : "storeToStoreSaleReceipt",
	"description" : "Payment",
	"prefix" : "FS",
	"createdAt" : ISODate("2022-07-02T03:08:47.789+09:00")
},

/* 6 createdAt:7/2/2022, 2:59:12 AM*/
{
	"_id" : ObjectId("62bf35f0c5779f926256db25"),
	"paid" : 0,
	"received" : 54700000,
	"total" : Double("7247129874"),
	"balance" : Double("7247129874"),
	"isDeleted" : false,
	"reference" : "1570",
	"storeId" : ObjectId("61cf4afdea853a367998f6e4"),
	"companyId" : ObjectId("60e5c27b5dd888474611b6a4"),
	"createdBy" : ObjectId("61b86f6e3d35436056f97522"),
	"recordType" : "storeToStoreSaleReceipt",
	"description" : "Payment",
	"prefix" : "FS",
	"createdAt" : ISODate("2022-07-02T02:59:12.689+09:00")
},

I am trying to calculate the new balance of the store when the store makes a payment or an invoice by getting the most recent balance of the store from the collection.

The balance for document with id “_id” : ObjectId(“62c02e08c5779f54ce56e42f”) should be calculated by getting the latest balance from document with id “_id” : ObjectId(“62bf382fc5779f83bd56dc6f”), but instead the balance from document with id “_id” : ObjectId(“62bf35f0c5779f926256db25”) was used. Therefore, the most recent balance is wrong.

Could you help me figure out what is wrong with my approach or whether there is another way to achieve this without having to make big changes in my code?

2

If you are running a replica set and you are reading on a secondary, it is possible that you do not get the latest document if the secondary is lagging the oplog of the primary.

Make sure you read from primary only.

Look at https://www.mongodb.com/docs/manual/applications/replication/ to see how to setup your writes and reads to make sure you get the data you want.

1 Like