mongoose add a field to a document from another document

user_9722233 0
Viewed 57

I want to get categories by limit, offset, orderBy viewers from VideoSchema

const CategoriesSchema = new Schema<ICategories>({
  name: String,
  image: String,
});

const VideoSchema = new Schema<IVideo>({
  videoSource: String,
  // from CategoriesSchema
  category: {
    name: String,
    image: String,
  },
  viewers: Number,
});

What i do to get what i want:

const resolver = async ({ args: { limit, offset } }) => {
      // there are 60k+ categories so request will be long
      const categories = await CategoriesModel.find();
      for (let i = 0; i < categories.length; i++) {
        const streams = await VideosModel.find({ 'category.name': categories[i].name });
        categories[i] = {
          ...categories[i],
          // count viewers of current video
          viewers: streams.reduce((acc, curr) => acc + curr.viewers, 0),
        };
      }
      // sort by viewers, slice array by limit and offset
      return categories.sort(({ viewers: viewersA = 0 }, { viewers: viewersB = 0 }) => viewersB - viewersA).slice(offset, limit);
  },

I understand that request is not optimized, but I can't figure out how to do request by limit and offset and sort it by current viewers count. I know that in relational database like mysql i can use left join for that case, but don't understand how to do something like that with mongoose

Clean up:

enter image description here

There is Categories list, each category have number of viewers from VideoSchema (viewers field). If there is no video for that category then should have 0 viewers or if we have video for that category we print number of viewers from 0 to 2^32

Also I have changes in VideoSchema, for now its with ref to Categories

const VideoSchema = new Schema<IVideo>({
  videoSource: String,
  // from CategoriesSchema
  category: {
    ref: 'Categories'
  },
  viewers: Number,
});
mongoose mongodb javascript
1 Answers

If I understand correctly, you want for each category, to add the sum of viewers from the relevant videos, sort and slice.

On mongoDB you can easily do it all in one query. Since you want to get categories with 0 viewers as well, we will start from the category collection:

  1. $lookup to get all videos per category and sum the number of viewers per each category inside the $lookup pipeline, to minimize data transformation between the collections.
  2. Format the response and handle cases of no video data
  3. $sort by number of viewers, $skip and $limit - for the slicing. Add the _id to the $sort as a second option, in order to keep it sorted even for 0 viewers categories.
db.category.aggregate([
  {
    $lookup: {
      from: "video",
      let: {category_id: "$_id"},
      pipeline: [
        {$match: {$expr: {$eq: ["$category", "$$category_id"]}}},
        {$group: {_id: 0, viewers: {$sum: "$viewers"}}}
      ],
      as: "viewers"
    }
  },
  {$set: {viewers: {$ifNull: [{$first: "$viewers.viewers" }, 0]}}},
  {$sort: {viewers: -1, _id: 1}},
  {$skip: offset},
  {$limit: limit}
])

See how it works on the playground example

user_184823100
Related