Search code examples
loopbackjs

loopback where filter on date's year

I am not sure if database functions are supported thru REST API requests or if I am doing something wrong. I am using mysql db connector.

I have tried using simple where filter with both equality and gte operators. 

{"where":{"YEAR(StartDate)":2018}}

or 

{"where":{"and":[{"YEAR(StartDate)":{"gte":2018}},{"year(StartDate)":{"lte":2020}}]}}

I don't get any error and the filter condition is silently ignored returning all records from the table.

Solution

  • You can't call functions from the query, you need to pass the values there. You may want to enable the debugger to see what is going on and what queries are actually triggered. So, assuming that field name in the database is StartDate, you probably want to use something like this:

    {"where":{ "StartDate": {"gte": new Date('2018-01-01T00:00:00.000Z')}} }

    or, if and statement involved

    {"where":{"and":[{"StartDate":{"gte":new Date('2018-01-01T00:00:00.000Z')}},{"StartDate":{"lte":new Date('2020-01-01T00:00:00.000Z')}}]}}