mongodb sort command to sort database on multiple conditions-CodePudding

So in my query im setting up for mongodb, im trying to find all products, that match a specific keyword or category, this works, what doesnt necessarily work is that i want to sort these products by the highest price, but only if i have these products in stock.

my current implementation looks as follows:

const getProducts = asyncHandler(async (req, res) => {
  try {
    const pageSize = 10;
    const page = Number(req.query.pageNumber) || 1;
    const keyword = req.query.keyword
      ? {
          name: {
            $regex: req.query.keyword,
            $options: "i",
          },
        }
      : {};
    const category = req.query.category
      ? { category: { $regex: req.query.category, $options: "i" } }
      : {};
    const count = await Product.countDocuments({ ...keyword, ...category });
    const products = await Product.find({ ...keyword, ...category })
      .limit(pageSize)
      .skip(pageSize * (page - 1))
      .sort({ countInStock: -1, createdAt: -1 })
      .populate("categories.category", "id cat_name slug");

    res.json({
      products,
      page,
      pages: Math.ceil(count / pageSize),
      prevPage: page - 1,
      nextPage: page   1,
    });
  } catch (error) {
    console.error(error);
    res.status(500).json({ message: `Server Error: ${error.message}` });
  }
});

at my .sort command whats happening is its sorting the query on the highest amount of items in stock first, regardless of their prices, and THEN sorting the rest of the products on their created date, which isnt the logic i want but its what im using now at present. I've seen a few SO articles that use the aggregate command to sort the database, but im not sure how to make that work on my current setup. because, doesnt aggregation ONLY return the match conditions? if the match conditions are an {} object will it still function the same way as a find() command?

---Update--- I've started work on implementing an aggregate command instead of a find() command, but, its... working exactly, the same as the find?

const products = await Product.aggregate([
      {
        $match: {
          $expr: { keyword, category },
        },
      },
      {
        $sort: {
          countInStock: -1,
          price: -1,
        },
      },
    ])
      .limit(pageSize)
      .skip(page)
      .populate("categories.category", "id cat_name slug");

its still not sorting on both conditions, its sorting the items, by countInStock and then price but not both at the same time. i want the highest priced item, i have in stock first, then the most... i might be over thinking this.

CodePudding user response：

They are both included in the sort, but the resultset is ordered according to the number of units in stock and then the price. This might make it seem like the price is ignored during sorting.

The usual workaround is to add a helper field using a $project or $addFields step. In this instance, it makes sense to add a field that is either 0 or 1 depending on the stock status.

await Product
  .aggregate([{
    $match: {
      $expr: {
        keyword,
        category
      },
    },
  }, {
    $addFields: {
      isInStock: { // Helper field. This is 1 if there are items in stock.
        $cond: {
          if: "$countInStock",
          then: 1,
          else: 0
        }
      }
    }
  }, {
    $sort: {
      isInStock: -1,
      price: -1,
    },
  }, {
    $skip: page
  }, {
    $limit: pageSize
  }])
  .populate("categories.category", "id cat_name slug");

Note that the model population may be written using the aggregate step $lookup, making the code more concise.