1

I'm trying to query data between datetime fields from Appsmith to MongoDB. I'm not able to get the data from this query, it is returning empty response but there is data in the selected datetime range.

The control flow looks like this:

  • There is a start time datetime picker field along with dropdown to select time period from start day(1 day, 1 week, 1 month).
  • On changing dropdown, it resets the table widget where I'm showing the data.
  • Table widget gets the data from call_data document but the query field is populated by a JSObject (queryObj.prepareQuery()).

JSObject code is:

export default {
    prepareQuery: () => {
        var callTypeSelected = call_type_select.selectedOptionValue;
        var customerId = customer_select.selectedOptionValue;
        var callStatus = call_status_select.selectedOptionValue;
        var timeRange = call_time_range.selectedOptionValue;
        var callTime = call_date_picker.selectedDate;
        var query = { };
        
        if (callTypeSelected !== null && callTypeSelected !== undefined && callTypeSelected !== "") {
            query["call_type"] = callTypeSelected;
        }
        
        if (customerId !== null && customerId !== undefined && customerId !== "") {
            query["strings_customer.$id"] = `ObjectId('${customerId}')`;
        }
        
        if (callStatus !== null && callStatus !== undefined && callStatus !== "") {
            query["call_status"] = callStatus;
        }
        
        if (timeRange !== null && timeRange !== undefined && timeRange !== "" && callTime !== null && callTime !== undefined) {
            var callTimeDateObj = new Date(callTime);
            
            var higherTime = new Date(callTimeDateObj);
            higherTime.setHours(higherTime.getHours() + 1);
            if (timeRange === "1 day") {
                higherTime.setHours(higherTime.getHours() + 24);
            } else if (timeRange === "1 week") {
                higherTime.setHours(higherTime.getHours() + 24 * 7);
            } else if (timeRange === "1 month") {
                higherTime.setHours(higherTime.getHours() + 24 * 30);
            }
            
            query["call_time"] = {"$gte": `ISODate('${callTimeDateObj.toISOString()}')`, "$lt": `ISODate('${higherTime.toISOString()}')`}
        }

        return query;
    }
}

Attaching the screenshots for more clarity:

enter image description here

enter image description here

enter image description here

enter image description here

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Shlok Walia
  • 11
  • 2

2 Answers2

1

It seems the quotes are not properly put. e.g. the date query should look like:

{
  "createdAt": {
    $gt: ISODate('2020-03-17T11:07:25.312Z'), 
    $lt: ISODate('2020-03-29T11:07:25.312Z')
  }
}

The video under the workaround section could also be helpful here: https://github.com/appsmithorg/appsmith/issues/10374

sumit
  • 23
  • 5
  • But for ObjectId it is working fine. I thought it should also work find for Datetime field. – Shlok Walia Jan 13 '22 at 06:50
  • Hi Shlok, thanks for bringing this up. Actually, Appsmith handles the quotes internally for `ObjectId` - such handling had not been added for `ISODate`. I have raised an issue to track it: https://github.com/appsmithorg/appsmith/issues/10374 – sumit Jan 13 '22 at 11:19
0

I was able to recreate your issue and get it working. Your query should look like -

{
  "release_date": {
    "$gte": "2020-10-16T13:02:04.044Z",
    "$lt": "2021-04-14T14:02:04.044Z"
  }
}

I've created a public app that you can fork and play around with - https://app.appsmith.com/applications/61e022f1eb0501052b9fa205/pages/61e02308eb0501052b9fa20c

Pranav Kanade
  • 1