My aim is to get books from the database on specific condition but at the same time i want to get their count as well. Below is my code. Here i have implemented pagination so i using limit() and skip() to get only required data for that particular page. I am doing a regex search on either title or author field and selecting documents for given genre. I am also sorting them in ascending or descending based on query.

const getAllBooks = asyncHandler(async (req, res) => {
  const page = req.query.page === undefined ? 1 : req.query.page;
  const limit = 5;
  const skip = page * limit - limit;
  const allGenres = ["action", "drama", "sci - fi", "romance", "comedy"];
  const reqGenre =
    req.query.genre === "" ? allGenres : req.query.genre.split(",");
  const search = req.query.search === undefined ? "" : req.query.search;
  const searchBy =
    req.query.searchBy === "title"
      ? { title: { $regex: search, $options: "i" } }
      : { author: { $regex: search, $options: "i" } };
  const sort = req.query.sort === "asc" ? 1 : -1;
  let sortBy;
  if (req.query.sortBy === undefined) {
    sortBy = { title: sort };
  } else if (req.query.sortBy === "author") {
    sortBy = { author: sort };
  } else if (req.query.sortBy === "title") {
    sortBy = { title: sort };
  }
  const books = await Book.find(searchBy)
    .where("genre")
    .in(reqGenre)
    .sort(sortBy)
    .skip(skip)
    .limit(limit)
    .select("-createdAt -updatedAt -__v");
  const documents = await Book.find(searchBy)
    .where("genre")
    .in(reqGenre)
    .sort(sortBy)
    .count();
  const total = documents / limit;
  res.status(200);
  res.json({ books, total });
})

Below is my model

const mongoose = require("mongoose");

const LibrarySchema = mongoose.Schema(
  {
    //denotes the title of the book
    title: {
      type: String,
      required: true,
    },
    // denotes the author of the book
    author: {
      type: String,
      required: true,
    },
    genre: {
      type: [String],
      required: true,
    },
  },
  {
    timestamps: true,
  }
);
module.exports = mongoose.model("LibraryModel", LibrarySchema);

Currently i am able to get books and their total for specific query on two separate calls to the database. But hypothetically if we have thousands of books in the database then this method will be very expensive. So i want to merge these both calls into one and get the result in one go. I tried to get a solution but could not find any. Any help is appreciated. Thanks in advance.

CodePudding user response：

I think you can use $facet to generate two outputs:

db.collection.aggregate([
  {
    "$match": {
      // your searchBy query here
  },
  {
    "$sort": {
      // your sortBy query here
    }
  },
  {
    "$facet": {
      "result": [
        {
          "$skip": // your skip
        },
        {
          "$limit": // your limit
        },
        {
          "$project": {
            // your select
          }
        }
      ],
      "count": [
        {
          "$count": "count"
        }
      ]
    }
  },
  {
    "$project": {
      "result": 1,
      "count": {
        "$arrayElemAt": [
          "$count",
          0
        ]
      }
    }
  }
])

Example here with predefined values in the quey, you can use the objects you have created.

You can change aggregate objects by JS objects you have and should works fine.

JS snippet to create the query dinamically:

// mock req to avoid problems
const req = {
    query: {
        genre: ""
    }
}

const page = req.query.page === undefined ? 1 : req.query.page;
const limit = 5;
const skip = page * limit - limit;
const allGenres = ["action", "drama", "sci - fi", "romance", "comedy"];
const reqGenre = req.query.genre === "" ? allGenres : req.query.genre.split(",");
const search = req.query.search === undefined ? "" : req.query.search;
let matchQuery = {"$match":{}}
const searchBy = req.query.searchBy === "title"
        ? matchQuery["$match"] = { title: { $regex: search, $options: "i" } }
        : matchQuery["$match"] = { author: { $regex: search, $options: "i" } };
matchQuery["$match"]["genre"] = {"$in": reqGenre}
const sort = req.query.sort === "asc" ? 1 : -1;
let sortBy;
if (req.query.sortBy === undefined) {
    sortBy = { title: sort };
} else if (req.query.sortBy === "author") {
    sortBy = { author: sort };
} else if (req.query.sortBy === "title") {
    sortBy = { title: sort };
}
const query = [matchQuery,
  {
    "$sort": sortBy
  },
  {
    "$facet": {
      "result": [
        {
          "$skip": skip
        },
        {
          "$limit": limit
        },
        {
          "$project": {
            "createdAt": 0,
            "-createdAt": 0,
            "-updatedAt": 0,
            "-__v": 0
          }
        }
      ],
      "count": [
        {
          "$count": "count"
        }
      ]
    }
  },
  {
    "$project": {
      "result": 1,
      "count": {
        "$arrayElemAt": [
          "$count",
          0
        ]
      }
    }
  }]

  console.log(JSON.stringify(query))

This snippet creates the query si you can simply do const result = await Book.aggregate(query).

Note how the console.log() output is a valid Mongo query in this example