Filtering by custom date column

Moralis General
#1

Hey, everyone! I have an issue with filtering by custom date column in aggregation pipeline. I pass a javascript date object into wageDeadline parameter. The output of the pipeline is empty even though it should not be. I also tried adding date filter into a $match in the pipeline but this did not work either. Does anyone have any ideas on what I am doing wrong?

//query Markets
Moralis.Cloud.define("getMarkets", async(request) => {
    const query = new Moralis.Query('Markets');

    //filter date if needed
    if(request.params.wageDeadline && (request.params.wageDeadline != 'All')){
        const wageDeadline = new Date(request.params.wageDeadline.toISOString())
        query.greaterThan('wageDeadline', wageDeadline);
    }

    //create end filters
    let m = {};
    if(request.params.asset && (request.params.asset != 'All')){
        m.asset = request.params.asset.toLowerCase();
    }
    if(request.params.tvl && (request.params.tvl != 'All')){
        m.tvl = {
            $gt: request.params.tvl
        };
    }if(request.params.tvl && (request.params.tvl != 'All')){
        m.wageDeadline = {
            $gt: request.params.wageDeadline
        };
    }

    const pipeline = [
        {
            lookup: {
              from: "Deposits",
              localField: "marketId",
              foreignField: "marketId",
              as: "deposits",
            },
        },
        {
            unwind: {
              path: "$deposits"
            }
        },
        {
            group: {
                'objectId': '$deposits.marketId',
                'marketId': {
                    $max: '$deposits.marketId'
                },
                'strikePrice': {
                    $max: '$strikePrice'
                },
                'tvl': {
                    $sum: {
                        $add: ['$deposits.moneyYes', '$deposits.moneyNo']
                    }
                },
                'asset': {
                    $max: '$asset'
                },
                'resolved': {
                    $max: '$resolved'
                },
                'resolutionDate': {
                    $max: {
                        $toDate: '$resolutionDate'
                    }
                },
                'wageDeadline': {
                    $max: {
                        $toDate: '$wageDeadline'
                    }
                },
            }
        },
        {
            sort: {
              tvl: -1
            }
        }
    ];

    if(m !== {}){
        pipeline.push(
            {match: m}
        );
    }

    const results = await query.aggregate(pipeline, { useMasterKey: true });
    return results;
});
#2

did you try to run the query only with that filter (without that pipeline) to see if it works as expected?

#3

I tried and it works

#4

now try to move only that filter in the pipeline

#5

I tried leaving only date filter in the pipeline but the result is still empty. Here is the code for this attempt.

//query Markets
Moralis.Cloud.define("getMarkets", async(request) => {
    const query = new Moralis.Query('Markets');
    const date = new Date(request.params.wageDeadline);
    const pipeline = [
        {
            match: {
                wageDeadline : {
                    $lt: date
                }
            }
        }

    const results = await query.aggregate(pipeline, { useMasterKey: true });
    logger.info('results');
    logger.info(results);
    return results;
});
#6

ok, now you have to try with different types of date: string, date, iso date, isostring until you match the type from the database

#7

Applying toString() to the js date object which I pass as a parameter resolved the problem. Thanks!