I have this stores collection with embedded docs cashierBoxes and cashierBoxes has embedded docs "inOutMovements"
I have been trying to make a query to get the inOutMovements sorted by date (descending) with pagination using ($sort, $skip and $limit) but I’m getting the error “Sort exceeded memory limit of 33554432 bytes”, I already added allowDiskUse(true) and an index to my store schema.
Am I doing something in the wrong order during aggregate?
await StoreModel.aggregate([
{
$match: {
_id: Types.ObjectId(storeId)
}
},
{
$unwind: {
path: '$cashierBoxes',
}
},
{
$match: {
"cashierBoxes._id": Types.ObjectId(id)
}
},
{
$unwind: {
path: '$cashierBoxes.inOutMovements'
}
},
{
$lookup: {
from: "user",
localField: "cashierBoxes.inOutMovements.createdBy",
foreignField: "_id",
as: "cashierBoxes.inOutMovements.createdBy",
pipeline: [
{
$project: {
_id: 1,
name: 1
}
}
]
},
},
{
$unwind: {
path: "$cashierBoxes.inOutMovements.createdBy",
preserveNullAndEmptyArrays: true,
},
},
{ $sort: { "cashierBoxes.inOutMovements.date": -1 } },
{ $skip: +pageNum * +limit },
{ $limit: +limit },
{
$group:
{
_id: {
_id: "$cashierBoxes._id",
name: "$cashierBoxes.name",
},
inOutMovements: {
$push: {
$cond: [
{ $gte: ["$cashierBoxes.inOutMovements.amount", 0] },
'$cashierBoxes.inOutMovements',
"$$REMOVE"
]
}
},
}
},
]).allowDiskUse(true);
const StoreSchema = new Schema({
...
cashierBoxes: {
type: [CashierBoxSchema],
required: true,
default: []
},
...
})
StoreSchema.index({
"_id": 1,
"cashierBoxes._id": 1,
"cashierBoxes.inOutMovements.date": - 1
})
const CashierBoxSchema = new Schema({
...
createdBy: {
ref: 'user',
type: Schema.Types.ObjectId,
},
inOutMovements: {
type: [CashierBoxActivity],
required: false,
default: []
},
...
})
const CashierBoxActivity = new Schema({
...
date: {
type: Date,
required: true
},
createdBy: {
ref: 'user',
type: Schema.Types.ObjectId,
required: false
}
...
});
