// Product
const ProductSchema = new Schema<TProductModel>(
{
name: String,
}
{ timestamps: true }
);
// OrderDetails
const OrderDetailsSchema = new Schema<TOrderDetailsModel>({
product: { type: Schema.Types.ObjectId, ref: 'Product' },
productsInOrder: { type: Number, default: 1 },
orderLimit: Number,
orderId: { type: Schema.Types.ObjectId, ref: 'Order' },
});
// Order
const OrderSchema = new Schema<TOrderModel>(
{
vendor: { type: Schema.Types.ObjectId, ref: 'Vendor' },
products: [{ type: Schema.Types.ObjectId, ref: 'OrderDetail'
}],
},
{ timestamps: true }
);
I need to find all Order
documents that have vendor
of vendorId
and whose products
don’t contain an OrderDetails
with a specific Product
Say I have an Order
:
const myOrder = {
"_id": "62975f946a2047c4e9c67ac6",
"status": "NEW",
"vendor": {
// vendor details
},
"products": [
{
"_id": "629763b74ede0232a7e8e2ab",
"product": "62975f476a2047c4e9c67ab1", // this is reference to "Tape" product
"productsInOrder": 5,
"orderLimit": 5,
"orderId": "62975f946a2047c4e9c67ac6",
"__v": 0
},
],
"createdAt": "2022-06-01T12:46:12.735Z",
"updatedAt": "2022-06-01T13:04:03.025Z",
"orderNumber": 18,
"__v": 3
}
When adding a new order of the same vendor
const newOrder = {
"_id": "629763c24ede0232a7e8e2bc",
"product": "62975f476a2047c4e9c67ab1", // this is reference to "Ladder" product
"productsInOrder": 1,
"orderLimit": 5,
"orderId": "62975f946a2047c4e9c67ac6",
"__v": 0
}
I want to find an Order
that doesn’t have yet OrderDetails
with product
set to "62975f476a2047c4e9c67ab1"
.
I tried
const order = await OrderModel.findOne({
vendor,
$ne: ['products.product', product],
});
but it returns an order that already have product product
, it looks like that:
{
_id: new ObjectId("62975f946a2047c4e9c67ac6"),
status: 'NEW',
vendor: new ObjectId("629581466e2469498cff053f"),
products: [
new ObjectId("62975f946a2047c4e9c67ac7"), // these 4 OrderDetails references all have the same `product`, so it shouldn't be found
new ObjectId("629763994ede0232a7e8e298"),
new ObjectId("629763b74ede0232a7e8e2ab"),
new ObjectId("629763c24ede0232a7e8e2bc")
],
createdAt: 2022-06-01T12:46:12.735Z,
updatedAt: 2022-06-01T13:04:03.025Z,
orderNumber: 18,
__v: 3
}
I also tried different aggregations but nothing worked.
I need a query that would find me an Order
that don’t include an OrderDetails
with a product
field equals to my provided productId
.
I think I need to somehow populate Order
‘s products
field with actual OrderDetails
objects before looking into its product
but I don’t know how to implement that.
2
Answers
you can try using MongoDB Aggregation Operations.
You need to see if IDs are stored as string or ObjectId in your schema.
Try it out and try to make more adjustments according to your needs. It’s just a sudo code
Useful references MongoDB $lookup pipeline match by _id not working https://www.mongodb.com/docs/manual/reference/operator/aggregation/ne/
https://www.mongodb.com/docs/manual/reference/operator/aggregation/filter/
Try to add some schema and docs for a better understanding of users. Hope this will be helpful.
If you already have an
order
collection with data like:For example, and you want to find a document with a
vendor.id: 12
which its products do not contain an item with"product": "62975f476a2047c4e9c67ab1"
, you can use$elemMatch
:Playground example
If you want to do it in the middle of an aggregation pipeline, you can use a
$filter
:Aggregation playground example