Mongoose Query Pattern
Tags: mongoose, beginners, node, javascript
Published: 2022-04-19 18:01:38

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](https://mongoosejs.com/docs/guide.html)

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](https://dev.to/kamruzzaman/simple-nodejs-mc-pattern-332d)

**Step-1**
download this project and install on your computer. [link](https://github.com/kamruzzamanripon/mongoose-query-pattern)

**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,
          });
        }
    }
```
if run this function on your postman which URL is http://localhost:5000/api/v1/all-user. you will see this output-

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8u1vgaoyx4ewalx1lud4.png)

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,
          });
        }
    }
```
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0hsah6fu3hy7hjna9c4e.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ppwzo8vawd04f6s2u8z.png)

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,
          });
        }
    }
```
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1t8pxmvkpfmis5fvzu0k.png)


**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,
          });
        }
    }
```
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bypiatvl2bhiihjp58zr.png)

```
{
            "_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"
                    }
                }
```
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,
          });
        }
    }
```
run this function on your postman which URL is http://localhost:5000/api/v1/invoice-all. you will see this output-

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uukhws19s4nghgjcs0mc.png)
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,
          });
        }
    }
```
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"
                }
            ]
        }
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pb5xaxpxj64psv52rdq8.png)

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