Query to find all documents that don't include an item of specific `_id`

user_12009306 0
Viewed 117
// 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.

mongoose mongodb-query mongodb
2 Answers

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.

user_138442030

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

user_184823100
Related