Question

0

160

Visualizações

Mongoose query - groupBy category and get last 4 items of each category

I am struggling in Writing that fetches 4 Products of each category. What I have done is

 exports.recentproducts = catchAsync(async (req, res, next) => {
     const doc = await Product.aggregate([
    { $sort: { date: -1 } },
    {
      $replaceRoot: {
        newRoot: {
          $mergeObjects: [{ $arrayElemAt: ['$products', 0] }, '$$ROOT'],
        },
      },
    },
    {
      $group: {    
        _id: '$productCategory',
        products: { $push: '$$ROOT' },
      },
    },

    {
      $project: {
        // pagination for products
        products: {
          $slice: ['$products', 4],
        },
        _id: 1,
       
      },
    },
    {
       $lookup: {
           from: 'Shop',
           localField: 'shopId',
           foreignField: '_id',
           as: 'shop',
     },
    },
  ]);

Document Model

const mongoose = require('mongoose');
   var ProductSchema = mongoose.Schema({
    title: {
        type: String,
        require: [true, 'Product must have a Title!'],
      },
      productCategory: {
        type: String,
        require: [true, 'Product must have a Category!'],
      },
      shopId: {
        type: mongoose.Schema.ObjectId,
        ref: 'Shop',
        require: [true, 'Product must have a Shop!'],
      },
    });
    
    var Product = mongoose.model('Product', ProductSchema);
    module.exports = Product;

expected result---

result= [
    {
        productCategory: "Graphics",
        products:[//4 products object here
           {
               must populate shop data
           }
       ]
    },
    {
        productCategory: "3d",
        products:[//4 products object here]
    },
       //there are seven categories I have like that
  ]

The Code i have done is working fine but it has two problems It does not populate shopId from Shop Model even through I have tried lookup It does not sort products in descending order(does not sort by date)

about 4 years ago · Juan Pablo Isaza

2 Respostas

Responde à pergunta

0

Query

in MongoDB 5 we can use $setWindowFields and $rank
partition by productCategory and sort by date descending
keep only rank <= 4 (4 latest products)
lookup to get the shop information
group by category and push all the information of product and shop

Test code here

Product.aggregate(
[{$setWindowFields:
  {partitionBy:"$productCategory",
   sortBy:{date:-1},
   output:{rank:{$rank:{}}}}},
 {$match:{rank:{$lte:4}}},
 {$lookup:
  {from:"Shop",
   localField:"shopId",
   foreignField:"_id",
   as:"shop"}},
 {$set:{shop:{$first:"$shop"}}},
 {$group:{_id:"$productCategory", products:{$push:"$$ROOT"}}}])

about 4 years ago · Juan Pablo Isaza Relatório

Responde à pergunta

Encontrar trabalhos remotos

Accepted Answer · 2022-08-31T08:12:44.464Z

There are few fixes in your implemented query,

$sort stage as it is,
$group stage as it is and moves to the second stage
$project stage as it is and move to third stage
$lookup with shop collection, pass products.shopId as localField
$project for merge shop object in products array
$map to iterate loop of products array
$filter to iterate loop of shop array return matching product
$arrayElemAt to get first element from above filtered result
$mergeOjects to merge current object with filtered shop object

const doc = await Product.aggregate([
  { $sort: { date: -1 } },
  {
    $group: {
      _id: "$productCategory",
      products: { $push: "$$ROOT" }
    }
  },
  {
    $project: {
      products: { $slice: ["$products", 4] }
    }
  },
  {
    $lookup: {
      from: "Shop",
      localField: "products.shopId",
      foreignField: "_id",
      as: "shop"
    }
  },
  {
    $project: {
      products: {
        $map: {
          input: "$products",
          in: {
            $mergeObjects: [
              "$$this",
              {
                shop: {
                  $arrayElemAt: [
                    {
                      $filter: {
                        input: "$shop",
                        as: "s",
                        cond: { $eq: ["$$s._id", "$$this.shopId"] }
                      }
                    },
                    0
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

0

160

Mongoose query - groupBy category and get last 4 items of each category

2 Respostas

0

0

Encontrar trabalhos remotos

Andres GPT