Sorting and limiting array objects of a MongoDB document

Question

I'm working on my first MERN fullstack project and I'm having problems with backend pagination, sorting and filtering.

In the project (an ecommerce) I saved the "Wishlist" document in the database. What I would like to achieve is:

1. Limiting the number of products saved in the wishlist visible in the frontend per page (example: 4 products per page);
2. Filtering the products according to the product name indicated by the user;
3. Sorting the products by date according to the order indicated by the user.

This is my db document example:

{
  _id: 618c1a00b3867b1fac2649d8,
  user: 610d77436cbf3d3580539ffe,
  wishlistItems: [
    {
      _id: 611c20a4fc79341104b2393b,
      name: 'Matcha BIO - 40 gm',
      slug: 'Matcha-BIO-40-gm',
      date: '2021-11-10T19:14:08.769Z',
      price: 18.9,
      discount: null,
      image: [Object],
      createdAt: 2021-11-10T19:14:08.986Z,
      updatedAt: 2021-11-10T19:14:08.986Z
    }
  ],
  createdAt: 2021-11-10T19:14:08.986Z,
  updatedAt: 2021-11-10T19:14:08.986Z,
  __v: 0
}

"wishlistItems" is the array that I would like to sort, filter and limit.

This is my get-wishlist controller in backend:

export const getWishListUser = async (req, res) => {
  try {
    const searchTerm = req.query.name;
    let sort = !req.query.sort ? -1 : req.query.sort;
    const page = parseInt(req.query.page) || 1;
    const pageSize = parseInt(req.query.limit) || 4;
    const skip = (page - 1) * pageSize;

    let totalWishlist = await WishlistUser.findOne({ user: req.user._id });
    

    if (totalWishlist) {
      const total = totalWishlist.wishlistItems.length;

      const pages = Math.ceil(total / pageSize);

      let result = await WishlistUser.aggregate([
        {
          $match: {
            $and: [
              { user: req.user._id },
              { "wishlistItems.name": { $regex: searchTerm, $options: "i" } },
            ],
          },
        },
        { $unwind: "$whishlistItems" },
        { $sort: { "wishlistItems.date": sort } },
        { $limit: pageSize },
        { $skip: skip },
        {
          $group: { user: "$user", wishlistItems: { $push: "$wishlistItems" } },
        },
      ]);

      console.log(result);
      if (result) {
        return res
          .status(200)
          .json({ wishlistItems: result, count: totalWishlist, page, pages });
      } else {
        return res
          .status(404)
          .json({ errorMessage: "User wishlist not found" });
      }
    } else {
      return res.status(404).json({ errorMessage: "User wishlist not found" });
    }
  } catch (error) {
    return res.status(500).json({ errorMessage: `${error}` });
  }
};

This is the frontend call:

 const getWishlistUser = async (name, sort, page) => {
  const response = await axios.get(
    `http://localhost:5020/api/user/wishlist/get-wishlist-user?name=${name}&sort=${sort}&page=${page}`,
    config2
  );
  return response;
};

 const gettingWishlistUser = (searchTerm, sort, page) => {
    setFavoriteProd({ ...favoriteProd, loading: true });
    getWishlistUser(searchTerm, sort, page)
      .then((response) => {
        setFavoriteProd({
          ...favoriteProd,
          wishlistItems: response.data.wishlistItems,
          count: response.data.count,
          loading: false,
        });
        setPages(response.data.pages);
      })
      .catch((error) => {
        setFavoriteProd({
          ...favoriteProd,
          errorMessage: error.toString(),
          loading: false,
        });
        setPages(1);
      });
  };

  useEffect(() => {
    if (isAuthenticated() && isAuthenticated().role === 0) {
      gettingWishlistUser(searchTerm, sort, page);
    }
  }, [page, sort, searchTerm, pages]);

How can I achieve the result? Thank you so much!

Answer 1

I think your main problem is that you are skiping after limit.

Think about this: If you first limit X values then you get as many as you want (as an example 10), and then you skip X elements... you are skiping from the remaining 10. You have to skip from all alements and limit as many as you want.

Using this query you can skip and limit:

db.collection.aggregate([
  {
    "$unwind": "$wishlistItems"
  },
  {
    "$sort": {
      "wishlistItems.date": 1
    }
  },
  {
    "$skip": 2
  },
  {
    "$limit": 2
  },
  {
    "$group": {
      "_id": "$_id",
      "user": {
        "$first": "$user"
      },
      "createdAt": {
        "$first": "$createdAt"
      },
      "updatedAt": {
        "$first": "$updatedAt"
      },
      "wishlistItems": {
        "$push": "$wishlistItems"
      }
    }
  }
])

Example here

Also, as an addition, an example where there is $limit before $skip here