Essentially what I want to achieve in Mongoose code, is the same as if I ran a query in MongoDB Compass with the Sort option set to {"Categories.0.updatedAt" : -1}.
In other words, in Mongoose, sort collection top-level documents by Categories.0.updatedAt.
API route in the back-end:
const express = require('express');
const router = express.Router();
const { MdProduct } = require('../models');
router.get('/', async (req, res, next) => {
try {
const { limit, offset, categoryFilter, maxPages, sort } = req.query;
let filter = {};
if (categoryFilter === 'null') {
filter = {
Categories: { $elemMatch: { CategoryId: null } },
DealID: { $ne: null }
};
} else if (categoryFilter === 'notNull') {
filter = {
Categories: { $elemMatch: { CategoryId: { $ne: null } } },
DealID: { $ne: null }
};
}
let sortOption;
switch(sort) {
case "Categories.0.UpdatedAt":
sortOption = { 'Categories.0.updatedAt': -1 };
break;
default:
sortOption = {};
}
const products = await MdProduct.find(filter)
.sort(sortOption)
.skip(Number(offset))
.limit(Number(limit) * Number(maxPages));
const results = products.map(product => {
let result = {
Images: product.Images?.[0]?.Src || null,
ProductSKU: product.ProductSKU,
Title: product.Title,
};
if (product.Categories && product.Categories.length > 0) {
result.categoryId = product.Categories[0].CategoryId;
result.breadcrumb = product.Categories[0].ExtraModelCategoryBreadcrumb;
} else {
result.categoryId = null;
result.breadcrumb = null;
}
return result;
});
res.json(results);
} catch (err) {
next(err);
}
});
module.exports = router;
On the front-end, it would be called like this:
useEffect(() => {
const fetchData = async () => {
try {
const response = await axios.get('/api/mapped-products?limit=10&offset=0&categoryFilter=notNull&maxPages=1&sort=Categories.0.UpdatedAt');
const rightData = response.data; // newly fetched data
console.log('rightData here: ', rightData);
const mergedRightData = mergeArrayOfObjects(rightData);
console.log('mergedRightData: ', mergedRightData);
// const flattenedRightData = flattenObject(rightData, true); // false meaning no categories
// console.log('flattenedRightData: ', flattenedRightData);
const removedTypename = mergedRightData.map(({ __typename, ...rest }) => rest)
const final = wrapInImageTags(removedTypename);
globalProps.mP.setRightTableRows(final);
// console.log('rightTableRows boi!: ', rightTableRows)
} catch (error) {
console.error('Error:', error);
}
};
fetchData();
}, [rightData]); // re-run the effect when `rightData` changes
For additional information, this is what an example document is structured like:
{
"_id": {
"$oid": "64657dbcf4a5caa85e8b79b9"
},
"ProductSKU": "test-32FSD",
"Brand": "Furi",
"BuyableProducts": [
{
"ExternalBuyableProductId": "12550922",
"SKU": "URO-FZ525",
"Price": 55,
"Quantity": 0,
"ListingStatus": "Live",
"Options": [],
"RRP": 55,
"ProductUnlimited": true,
"MetaInfo": {
"variationimageurl": "https://test.com.au/wp-content/uploads/2023/05/fz525_1.jpg",
"shippingweight": null,
"shippingheight": null,
"shippingwidth": null,
"shippinglength": null,
"gtin": null,
"mpn": null,
"_id": {
"$oid": "6495aa8325ce469662f09aef"
}
},
"_id": {
"$oid": "6495aa8325ce469662f09aee"
}
}
],
"Categories": [
{
"CategoryId": 1462,
"ExtraModelCategoryBreadcrumb": "Home & Garden > Kitchenware > Kitchen Knives > Bread Knives",
"createdAt": {
"$date": "2023-05-18T01:22:03.905Z"
},
"updatedAt": {
"$date": "2023-06-26T03:04:53.051Z"
}
}
],
"Condition": null,
"CustomFreightSchemeID": "1275",
"DeliveryTime": "7-21 business days",
"Description": "<p>Engineered for design, innovation, and performance. Chosen by leading chefs. We are Furi. Innovation is our story. It is built into our DNA and at the core of everything we do. We have a rebellious instinct to discard old ways and to think differently. We know how to help solve every day modern problems. The real ones that home cooks and professionals face in their kitchens 24/7.</p>\n<p>\nProduct features</p>\n<ul>\n<li>\nMaterial Stainless Steel</li>\n<li>\nStainless Steel</li>\n<li>\n25 Year Guarantee</li>\n<li>\nDurable</li>\n</ul>\n",
"DimensionsUnit": "cm",
"ExternalProductId": "12550922",
"GTIN": null,
"Has48HoursDispatch": false,
"Height": null,
"Images": [
{
"Id": 13383613,
"Src": "https://test.com.au/wp-content/uploads/2023/05/fz525_1.jpg",
"Position": 0,
"_id": {
"$oid": "6495aa8325ce469662f09af0"
}
}
],
"IsDirectImport": false,
"Length": null,
"MPN": "FZ525",
"MaxDaysForDelivery": 60,
"ProductSpecifics": null,
"RequiresShipping": true,
"ShippingCostCategory": "Custom",
"ShippingCostStandard": null,
"Specifications": "",
"Tags": "",
"Title": "Pro Bread Knife 200mm FZ525",
"Weight": null,
"WeightUnit": "kilograms",
"Width": null,
"createdAt": {
"$date": "2023-05-18T01:22:03.905Z"
},
"extraModelWooCommerceProductCategories": [],
"updatedAt": {
"$date": "2023-06-26T03:04:53.051Z"
}
}
The result is that it does not return documents in descending order of Categories.0.UpdatedAt (most recent returned first), as desired.
Please advise how to get this working as intended.