Query on date field with aggregate queries

Moralis General
#1

I am trying to query a date field (deadline) using the following query but it returns an empty list. We expect it to return some values in the list. Is this the right way to query on a date field?

Moralis.Cloud.define("filterJobsNew", async (request) => {
  const query = new Moralis.Query("Job");

  logger.info(`lower bound date ${request.params.deadlineFilter[0]}`);
  logger.info(`upper bound date ${request.params.deadlineFilter[1]}`);

  let sortOrder = 1;
  if (request.params.sortOrder === "desc") {
    sortOrder = -1;
  }

  const pipelineWithJobType = [
    {
      match: {
        $expr: {
          $and: [
            { $eq: ["$status", 0] },
            { $gte: ["$desiredMinLockedStake", request.params.lockedStake[0]] },
            { $lte: ["$desiredMinLockedStake", request.params.lockedStake[1]] },
            {
              $gte: ["$deadline", new Date(request.params.deadlineFilter[0])],
            },
            {
              $lte: ["$deadline", new Date(request.params.deadlineFilter[1])],
            },
            { $in: ["$type", request.params.jobType] },
          ],
        },
      },
    },

    {
      lookup: {
        from: "UserInfo",
        localField: "clientUsername",
        foreignField: "spectUsername",
        as: "user",
      },
    },

    {
      sort: {
        [request.params.sortBy]: sortOrder,
      },
    },
  ];

    return await query.aggregate(pipelineWithJobType);
});

Here is the request params being sent -

Input: {"ethAddress":"0x92d202402068108f3301e3c0bcf8b77f4a94a2a7","mainFilter":"all","jobType":[],"lockedStake":[0,9007199254740991],"deadlineFilter":["2021-10-04T17:56:55.080Z","2022-10-04T17:56:55.080Z"],"sortBy":"desiredMaxPrice","sortOrder":"desc"}
#2

Hey @adityachakra16

I just checked your server logs and I see that it returns you results. Could you share the way you call it and full response

#3

Hey @Yomoo

Sorry, the last server logs was from when I ran it by commenting out the deadline query. Here is the log when I run it with the deadline query present:

2021-10-05T00:46:15.308Z - Ran cloud function filterJobsNew for user undefined with: Input: {“ethAddress”:“0x92d202402068108f3301e3c0bcf8b77f4a94a2a7”,“mainFilter”:“all”,“jobType”:[],“lockedStake”:[0,9007199254740991],“deadlineFilter”:[“2021-10-05T00:46:15.074Z”,“2022-10-05T00:46:15.074Z”],“sortBy”:“desiredMaxPrice”,“sortOrder”:“desc”} Result: []
2021-10-05T00:46:15.293Z - upper bound date Wed Oct 05 2022 00:46:15 GMT+0000 (Coordinated Universal Time)
2021-10-05T00:46:15.292Z - lower bound date Tue Oct 05 2021 00:46:15 GMT+0000 (Coordinated Universal Time)

#4

Here is the full cloud function -

Moralis.Cloud.define("filterJobsNew", async (request) => {
  const query = new Moralis.Query("Job");

  logger.info(`lower bound date ${request.params.deadlineFilter[0]}`);
  logger.info(`upper bound date ${request.params.deadlineFilter[1]}`);

  let sortOrder = 1;
  if (request.params.sortOrder === "desc") {
    sortOrder = -1;
  }

  const pipelineWithJobType = [
    {
      match: {
        $expr: {
          $and: [
            { $eq: ["$status", 0] },
            { $gte: ["$desiredMinLockedStake", request.params.lockedStake[0]] },
            { $lte: ["$desiredMinLockedStake", request.params.lockedStake[1]] },
            {
              $gte: ["$deadline", new Date(request.params.deadlineFilter[0])],
            },
            {
              $lte: ["$deadline", new Date(request.params.deadlineFilter[1])],
            },
            { $in: ["$type", request.params.jobType] },
          ],
        },
      },
    },

    {
      lookup: {
        from: "UserInfo",
        localField: "clientUsername",
        foreignField: "spectUsername",
        as: "user",
      },
    },

    {
      sort: {
        [request.params.sortBy]: sortOrder,
      },
    },
  ];

  const pipelineWithoutJobType = [
    {
      match: {
        $expr: {
          $and: [
            { $eq: ["$status", 0] },
            { $gte: ["$desiredMinLockedStake", request.params.lockedStake[0]] },
            { $lte: ["$desiredMinLockedStake", request.params.lockedStake[1]] },
            {
              $gte: ["$deadline", new Date(request.params.deadlineFilter[0])],
            },
            {
              $lte: ["$deadline", new Date(request.params.deadlineFilter[1])],
            },
          ],
        },
      },
    },

    {
      lookup: {
        from: "UserInfo",
        localField: "clientUsername",
        foreignField: "spectUsername",
        as: "user",
      },
    },
    {
      sort: {
        [request.params.sortBy]: sortOrder,
      },
    },
  ];

  if (request.params.jobType.length > 0) {
    return await query.aggregate(pipelineWithJobType);
  } else {
    return await query.aggregate(pipelineWithoutJobType);
  }
});

This is how it is being called -

export async function filterJobs(
  mainFilter,
  jobTypeFilter,
  lockedStakeFilter,
  deadlineFilter,
  sortBy,
  sortOrder
) {
  var dateLowerBound = new Date(
    new Date().getTime() + deadlineFilter[0] * 24 * 60 * 60 * 1000
  );
  var dateUpperBound = new Date(
    new Date().getTime() + deadlineFilter[1] * 24 * 60 * 60 * 1000
  );

  const params = {
    ethAddress: "0x92d202402068108f3301e3c0bcf8b77f4a94a2a7",
    mainFilter: mainFilter,
    jobType: jobTypeFilter,
    lockedStake: lockedStakeFilter,
    deadlineFilter: [dateLowerBound, dateUpperBound],
    sortBy: sortBy,
    sortOrder: sortOrder,
  };

  const res = await Moralis.Cloud.run("filterJobsNew", params);
  console.log(`res right after db call ${res}`);

  return res;
}

I am expecting to see 4 records with this query.

#5

Did you ever get your query working? I know this post is old, but I’ve had issues with both Dates and using $in on aggregates.

For the dates, try keeping your dateLowerBound and dateUpperBound as timestamps instead of creating a new Date from your calculation, then in your aggregate pipeline, change "$deadline" to { $toLong: "$deadline" }.

When I use $in (which I also tried using inside an $and like in your pipelineWithJobType), I always get 0 results. If you figured this one out, please let me know!

#6

Hey @ChrisBrech

I saw you making an inquiry related to this Solution $lte and $gte Dates in aggregates

Let’s continue our conversation there instead as this is an old thread, I’ll be closing this.

#7