DEV Community

syed kamruzzaman
syed kamruzzaman

Posted on

Mongoose Query Pattern

Mongoose query is also a function that is related to MongoDB. If you want to fetch data from MongoDB then you can use mongoose.
Mongoose has a different type of query. You can find all details in Mongoose documentation. Link

Most beginners know about the mongoose CRUD function. Today, I am not saying about CRUD function. Trying to say a little bit different query. I hope you like this query. let's start.

I hope you also know, how to connect MongoDB Database. If you don't know, then you can read this article. Link

Step-1
download this project and install on your computer. link

Step-2
First talk about Models. We have Five Models[src/models]. Which are-

1. User - Its has name, email, phone
2. Product - it has name, catId reference by Category collection and subCatId reference by SubCategory collection and shopName.
3. Category- it has only name.
4. SubCategory - it has name and catId reference by Category collection.
5. Invoice - userId reference by User Collection and productId
reference by Product Collection.

this Models related dummy data include on dummy Folder. you just import this data onto your MongoDB database.

Step-3
open ProductController.js file [src/controllers/ProductController.js]. First I am talking this function

static allProductWithOutPagination = async(req, res)=>{
        try{
            const allProductInfo = await Product.find().exec();
            return res.status(200).json({
              code: 200,
              message: "Product List",
              data: allProductInfo,
            });

        }catch(error){
          res.status(501).json({
            code: 501,
            message: error.message,
            error: true,
          });
        }
    }
Enter fullscreen mode Exit fullscreen mode

if run this function on your postman which URL is http://localhost:5000/api/v1/all-user. you will see this output-

Image description

This Query fetch all product information from MongoDB. But, I know you are not satisfy this output. Because catId and subCatId are not showing full details. So what do we do. Lets go to 2nd function which is-

static allProductWithOutPaginationAndJoinCollection = async(req, res)=>{
        try{
            //const Users = await Product.find().exec();
            const allProductInfo = await Product.aggregate([
                {$lookup:
                    {
                       from: "categories",   // collection to join
                       localField: "catId",  //this field as refereence for category collection id
                       foreignField: "_id",  //this field is category id to join product collecton on base one catId === _id;
                       as: "categoryInfo"    // output array field
                    }
                },
                {   $unwind:"$categoryInfo" },
                {
                  $lookup: {
                      from: "subCategories",
                      localField: "subCatId",
                      foreignField: "_id",
                      as: "subCategoryInfo"
                  }
                },
                {   $unwind:"$subCategoryInfo" },
                //For presentation
                {
                  $project: {
                  "_id": 1,
                  "name": 1,
                  "shopName": 1,
                  "categoryInfo._id": 1,
                  "categoryInfo.name": 1,
                  "subCategoryInfo._id": 1,
                  "subCategoryInfo.name": 1,

                }
              }
            ]
            ).exec();

            return res.status(200).json({
              code: 200,
              message: "Product List",
              data: allProductInfo,
            });

        }catch(error){
          res.status(501).json({
            code: 501,
            message: error.message,
            error: true,
          });
        }
    }
Enter fullscreen mode Exit fullscreen mode

if run this function on your postman which URL is http://localhost:5000/api/v1/all-product-without-paination-join-collection. you will see this output-

Image description

Now, we can see details about catId and subCatId .

I think you are happy to see this result. But I am not. I want more. like as, i want pagination. ok, lets do it
first you will need "mongoose-aggregate-paginate-v2" plugins. you can find npm site. then add this plugins into your Product models.

Image description

Go again ProductController and write this code-

static allProductWithPaginationAndJoinCollection = async(req, res)=>{
        try{
            //const Users = await Product.find().exec();
            const options = {
                page: 1,
                limit: 10,
              };
            const allProductInfo =  Product.aggregate([
                {$lookup:
                    {
                       from: "categories",   // collection to join
                       localField: "catId",  //this field as refereence for category collection id
                       foreignField: "_id",  //this field is category id to join product collecton on base one catId === _id;
                       as: "categoryInfo"    // output array field
                    }
                },
                {   $unwind:"$categoryInfo" },
                {
                  $lookup: {
                      from: "subCategories",
                      localField: "subCatId",
                      foreignField: "_id",
                      as: "subCategoryInfo"
                  }
                },
                {   $unwind:"$subCategoryInfo" },
                //For presentaiton
                {
                  $project: {
                  "_id": 1,
                  "name": 1,
                  "shopName": 1,
                  "categoryInfo._id": 1,
                  "categoryInfo.name": 1,
                  "subCategoryInfo._id": 1,
                  "subCategoryInfo.name": 1,

                }
              }
            ]
            )

            const productInfoAddPagination =  await Product.aggregatePaginate(allProductInfo, options);
            //return console.log(productInfoAddPagination)

            return res.status(200).json({
              code: 200,
              message: "Product List with pagination",
              data: productInfoAddPagination,
            });

        }catch(error){
          res.status(501).json({
            code: 501,
            message: error.message,
            error: true,
          });
        }
    }
Enter fullscreen mode Exit fullscreen mode

if run this function on your postman which URL is http://localhost:5000/api/v1/all-product-with-paination-join-collection. you will see this output-

Image description

you also know about the pagination. Now we will go little bit tough query. Suppose, you will want shop name wise product and also this shop under total product. then what do you do. simple write this code-

static groupWiseProductInfoWithPaginationAndJoinCollection = async(req, res)=>{
        try{

            const allProductInfo = await Product.aggregate([
                {$lookup:
                    {
                       from: "categories",   // collection to join
                       localField: "catId",  //this field as refereence for category collection id
                       foreignField: "_id",  //this field is category id to join product collecton on base one catId === _id;
                       as: "categoryInfo"    // output array field
                    }
                },
                {   $unwind:"$categoryInfo" },
                {
                  $lookup: {
                      from: "subCategories",
                      localField: "subCatId",
                      foreignField: "_id",
                      as: "subCategoryInfo"
                  }
                },
                {   $unwind:"$subCategoryInfo" },
                //For presentaiton
                {
                  $project: {
                  "_id": 1,
                  "name": 1,
                  "shopName": 1,
                  "categoryInfo._id": 1,
                  "categoryInfo.name": 1,
                  "subCategoryInfo._id": 1,
                  "subCategoryInfo.name": 1,

                }
              },
              {
                $group: {
                    _id: { shopName: "$shopName" },
                    count: {$sum: 1},
                    details: { $push: '$$ROOT' },
                   },
              }
            ]
            ).exec();

            return res.status(200).json({
              code: 200,
              message: "Group wise Product List",
              data: allProductInfo,
            });

        }catch(error){
          res.status(501).json({
            code: 501,
            message: error.message,
            error: true,
          });
        }
    }
Enter fullscreen mode Exit fullscreen mode

if run this function on your postman which URL is http://localhost:5000/api/v1/group-wise-product-with-paination-join-collection. you will see this output-

Image description

{
            "_id": {
                "shopName": "Effertz"
            },
            "count": 12,
            "details": [
                {
                    "_id": "625b1961a45d3893f8b23191",
                    "name": "Treeflex",
                    "shopName": "Effertz",
                    "categoryInfo": {
                        "_id": "625b0e6aa45d3893f8b2308f",
                        "name": "Zamit"
                    },
                    "subCategoryInfo": {
                        "_id": "625b117da45d3893f8b230c5",
                        "name": "Naturasil"
                    }
                },
                {
                    "_id": "625b1961a45d3893f8b23192",
                    "name": "Overhold",
                    "shopName": "Effertz",
                    "categoryInfo": {
                        "_id": "625b0e6aa45d3893f8b2308b",
                        "name": "Y-find"
                    },
                    "subCategoryInfo": {
                        "_id": "625b117da45d3893f8b230cf",
                        "name": "Loratadine"
                    }
                }
Enter fullscreen mode Exit fullscreen mode

Now you see the powers of mongoose query. its really awesome.

Another last query we see. Go to InvoiceController.js file [src/controllers/InvoiceController.js] and see this code-

static invoiceAll = async(req, res)=>{
        try{
            const invoiceAll = await Invoice.find().exec();
            return res.status(200).json({
              code: 200,
              message: "All invoice",
              data: invoiceAll,
            });

        }catch(error){
          res.status(501).json({
            code: 501,
            message: error.message,
            error: true,
          });
        }
    }
Enter fullscreen mode Exit fullscreen mode

run this function on your postman which URL is http://localhost:5000/api/v1/invoice-all. you will see this output-

Image description
It's Totally Rubbish output. don't show proper output. let's do clear this output

static invoiceAllInfoDetails = async(req, res)=>{
        try{
            const invoiceAllInfoDetails = await await Invoice.aggregate([
                {$lookup:
                    //join users collection
                    {
                       from: "users",   // collection to join
                       localField: "userId",  //this field as refereence for User collection id
                       foreignField: "_id",  //this field is category id to join product collecton on base one catId === _id;
                       as: "userInfo"    // output array field
                    }
                },
                //join products collection
                {$lookup: {
                    from: "products",
                    let: { "products": "$productId" },
                    pipeline: [
                        { $match: { $expr: { $in: [ "$_id", "$$products" ] } } }
                    ],
                    as: "productInfo"
                  }
                },
                //join categories collection
                {$lookup:
                    {
                        from: "categories",   
                        localField: "productInfo.catId",  
                        foreignField: "_id",  
                        as: "categoryInfo"    
                    }
                },
                //join subCategory collection 
                {$lookup:
                    {
                        from: "subCategories",   
                        localField: "productInfo.subCatId",  
                        foreignField: "_id",  
                        as: "subCategoryInfo"    
                    }
                }

             ]
            ).exec();

        return res.status(200).json({
              code: 200,
              message: "All invoice with details information",
              data: invoiceAllInfoDetails,
            });

        }catch(error){
          res.status(501).json({
            code: 501,
            message: error.message,
            error: true,
          });
        }
    }
Enter fullscreen mode Exit fullscreen mode

run this function on your postman which URL is http://localhost:5000/api/v1/invoice-all-with-details-info. you will see this output-

{
            "_id": "625c7ab23ab63761bf85b143",
            "userId": "625b0dd5a45d3893f8b2304b",
            "productId": [
                "625b1961a45d3893f8b230e7",
                "625b1961a45d3893f8b230e8"
            ],
            "date": "3/12/2022",
            "userInfo": [
                {
                    "_id": "625b0dd5a45d3893f8b2304b",
                    "name": "Tú",
                    "email": "ebrauner3@admin.ch",
                    "phone": "260-756-6184"
                }
            ],
            "productInfo": [
                {
                    "_id": "625b1961a45d3893f8b230e7",
                    "name": "Kanlam",
                    "catId": "625b0e6aa45d3893f8b2307f",
                    "subCatId": "625b117da45d3893f8b230a6",
                    "shopName": "Kling"
                },
                {
                    "_id": "625b1961a45d3893f8b230e8",
                    "name": "Fix San",
                    "catId": "625b0e6aa45d3893f8b2307f",
                    "subCatId": "625b117da45d3893f8b230a6",
                    "shopName": "Kling"
                }
            ],
            "categoryInfo": [
                {
                    "_id": "625b0e6aa45d3893f8b2307f",
                    "name": "Sonair"
                }
            ],
            "subCategoryInfo": [
                {
                    "_id": "625b117da45d3893f8b230a6",
                    "name": "Metronidazole",
                    "catId": "625b0e6aa45d3893f8b2307f"
                }
            ]
        }
Enter fullscreen mode Exit fullscreen mode

Image description

Now take some rest and refresh your brain. Another day we will talk more details mongoose query.

[if it is helpful, giving a star to the repository 😇]
https://github.com/kamruzzamanripon/mongoose-query-pattern

Discussion (0)