I have Product collection. In this collection each document has same keys and different values. Several documents are shown in the example below.

[
  {
    "productCategory": "Electronics",
    "price": "20",
    "priceCondition": "Fixed",
    "adCategory": "Sale",
    "productCondition": "New",
    "addDescription": "Lorem Ipsum Dolor Sit Amet Consectetur Adipisicing Elit Maxime Ab Nesciunt Dignissimos.",
    "city": "Los Angeles",
    "rating": {
      "oneStar": 1,
      "twoStar": 32,
      "threeStar": 13,
      "fourStar": 44,
      "fiveStar": 1
    },
    "click": 12,
    "views": 3
  },
  {
    "productCategory": "Automobiles",
    "price": "1500",
    "priceCondition": "Negotiable",
    "adCategory": "Rent",
    "productCondition": "New",
    "addDescription": "Lorem Ipsum Dolor Sit Amet Consectetur Adipisicing Elit 
  Maxime Ab Nesciunt Dignissimos.",
    "city": "California",
    "rating": {
      "oneStar": 2,
      "twoStar": 13,
      "threeStar": 10,
      "fourStar": 50,
      "fiveStar": 4
    },
    "click": 22,}
  },
  {
    "productCategory": "Hospitality",
    "price": "500",
    "priceCondition": "Yearly",
    "adCategory": "Booking",
    "productCondition": "New",
    "addDescription": "Lorem Ipsum Dolor Sit Amet Consectetur Adipisicing Elit Maxime Ab Nesciunt Dignissimos.",
    "city": "Houston",
    "rating": {
      "oneStar": 16,
      "twoStar": 19,
      "threeStar": 28,
      "fourStar": 16,
      "fiveStar": 17
    },
    "click": 102,
    "views": 47
  }
]

I would like to search each document with one or more matching search queries to match the document with my search.

In the example below, I will show url query: http://localhost:8080/api/v1/filter?productCondition=New&price=100&productCategory=Hospitality

So far I have tried to solve the filtration using the $or and $and operators but here there is a problem because with the first operator $or when I do a query only the first condition is searched, the rest is omitted, with the second operator $and I have to add all the condition and the problem arises because I will not always have all querys.

I am using the find method to achieve my filtering methods.

db.collection(Index.Add)
  .find({
    $or: [
      { productCategory },
      { price },
      { adCategory },
      { priceCondition },
      { productCondition },
      { city },
    ],
  })
  .limit(pageSize)
  .skip(pageSize * parsePage)
  .toArray();

db.collection(Index.Add)
  .find({
    $and: [
      { productCategory },
      { price },
      { adCategory },
      { priceCondition },
      { productCondition },
      { city },
    ],
  })
  .limit(pageSize)
  .skip(pageSize * parsePage)
  .toArray();

CodePudding user response：

From what I understand, you're trying to build filters for your products listing API. On any listing page, if one applies two or more simultaneous filters one expects an and of all those filters. So, a query like this productCondition=New&price=100 should get all the products where productCondition is New and price is 100.

Therefore, in order to filter we should go for an $and operator in find query. As you rightly said you might not have all the query params in your path always, so a condition like this:

$and: [
  { productCategory },
  { price },
  { adCategory },
  { priceCondition },
  { productCondition },
  { city },
]

would send null or undefined to the database values for the keys that don't exist in the query param and you might get unexpected results.

To handle this, you should build the $and query based upon what is there in your query params like this (assuming you're using express.js):

let conditions = [];
for(const param in req.query) {
  if(req.query[param]) {
    conditions.push({ param: req.query[param] });
  }
}

conditions will only contain the keys whose values are there in the query params. You can then pass the conditions variable to the query like this:

db.collection(Index.Add)
  .find({
    $and: conditions
  })
  .limit(pageSize)
  .skip(pageSize * parsePage)
  .toArray();

CodePudding user response：

Thank you for bringing me to a solution. My solution is a banned query that is undefined. And just apply the valid query to find method. Example:

const {
  page,
  productCategory,
  price,
  priceCondition,
  adCategory,
  productCondition,
  city,
}: IAdds = req.query;

const check = (object: IAdds) => {
  for (let key in {
    productCategory,
    price,
    priceCondition,
    adCategory,
    productCondition,
    city,
  }) {
    if (object[key] !== undefined) return req.query;
  }
};
const resultFromCheckQuey = check(req.query);
const { page, ...res } = resultFromCheckQuey;
const filterResult = await db
    .collection(Index.Add)
    .find({
      $and: [res],
    })
    .limit(pageSize)
    .skip(pageSize * parsePage)
    .toArray();