I have a "hackathon" model that looks like this:

I need to paginate the data, but right now I'm querying the database based on the amountInPrizes range and a list of tags set by the user on the frontend first, and then filtering the array manually based on the rest of the fields specified by the user (since firestore doesn't allow multiple inequality queries on different fields and I couldn't find another way around it)

Here's what the route looks like:

const {minPrize, maxPrize, inPerson, online, startDateString, endDateString, tags} = req.query;
  
  try {
    let hackathonQuery = db.collection("hackathons");
    
    if (minPrize && minPrize !== "") {
      hackathonQuery = hackathonQuery.where("amountInPrizes", ">=", Number.parseInt(minPrize));
    }

    if (maxPrize && maxPrize !== "") {
      hackathonQuery = hackathonQuery.where("amountInPrizes", "<=", Number.parseInt(maxPrize));
    }
    
    if (tags && tags !== "") {
      const tagsList = tags.split(",");
      if (tagsList.length >= 1) {
        hackathonQuery = hackathonQuery.where("tags", "array-contains-any", tagsList);
      }
    }
    
    // this is where the pagination should be, but there's still a bunch of manual filters after, so the pagination wouldn't be accurate 
    const hackathonsSnapshot = await hackathonQuery.orderBy("amountInPrizes", "desc").get();
    const hackathons = [];

    hackathonsSnapshot.forEach(doc => {
      const hackathon = doc.data();

      if (startDateString && startDateString !== "") {
        const startDate = new Date(startDateString);

        if (new Date(hackathon.startDate).getTime() < startDate.getTime()) {
          return;
        }                
      }
  
      if (endDateString && endDateString !== "") {
        const endDate = new Date(endDateString);

        if (new Date(hackathon.endDate).getTime() > endDate.getTime()) {
          return;
        }    
      }
    
      if (inPerson && inPerson === "true") {
        // to make it so that if both are selected it queries correctly
        if (online !== "true") {
          if (hackathon.location == "Online") {
            return;
          }
        }
      }
  
      if (online && online === "true") {
        // to make it so that if both are selected it queries correctly
        if (inPerson !== "true") {
          if (hackathon.location !== "Online") {
            return;
          }
        }
      } 
      
      hackathons.push({
        id: doc.id,
        ...hackathon,
      })
    })
    
    return res.status(200).json({hackathons});
  } catch (err) {
    console.log(err.message);
    res.status(400).send("Server error");
  }

If I were to add a .limit() on my initial query, an inconsistent number of documents would be filtered out by the rest of the manual filters on each page of hackathons. Is there a way around this or a better way to do this whole thing?

(Sorry if that explanation was confusing)

CodePudding user response：

If you really need full flexibility on the user input, then there is no way around it. If you can change the model and change the user interface a bit there are some things you can do:

1. add onlineLocation: boolean to the model and when saving the document set it to true/false according to the data. This will eliminate your online filtering and will allow you to add a where... to the query (you will not need to use the inequality).

2. Date range - as I understand the user can select a start date. If it is acceptable to change the interface to allow the user to select from predefined values (like: last week, last month, last year... etc.) what you can do is save in addition to the start date:

• startDayIndex = Math.floor(startDate.getTime() / millisecondsInDay) this results in the day index since Jan 1st 1970
• startWeekIndex = Math.floor(startDate.getTime() / millisecondsInWeek) this results in the week index since Jan 1st 1970 etc... this will allow you to query firestore with equality query (get all documents that the start date is 3 weeks ago)

I'm not sure if this is applicable for your use case. If the date range is important to keep as is, you can change the amountInPrizes to be ranges instead of numbers. This way the user can get all documents that the amountInPrizes is between 1000-2000 for example. again you will need to add a field to your model, say: prizeRange, and query that field with equality query, leaving the date in range query