// 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.

CodePudding user response：

you can try using MongoDB Aggregation Operations. You need to see if IDs are stored as string or ObjectId in your schema.

products.aggregate([
 {
  { $lookup: {
   from: "product",
   let: { "id": "$_id.product" },
   pipeline: [
     { $match: { "$expr": { "$ne": ["<your ID>", "$$id"] }}},
    ],
   as: "output"
  }}
]);

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.

CodePudding user response：

If you already have an order collection with data like:

{
    "_id": "62975f946a2047c4e9c67ac6",
    "status": "NEW",
    "vendor": {id: 12},
    "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,
}

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:

db.orders.find({
  "vendor.id": 12,
  products: {$not: {$elemMatch: {product: "62975f476a2047c4e9c67ab1"}}}
})

Playground example

If you want to do it in the middle of an aggregation pipeline, you can use a $filter:

db.orders.aggregate([
  {$match: {"vendor.id": 12}},
  {
    $addFields: {
      removeProducts: {
        $size: {
          $filter: {
            input: "$products",
            as: "item",
            cond: {$eq: ["$$item.product", "62975f476a2047c4e9c67ab1"]}
          }
        }
      }
    }
  },
  {$match: {removeProducts: 0}},
  {$unset: "removeProducts"}
])

Aggregation playground example