Where::make() does not support [gte]/[lte] range filtering as expected

[Redkev007]

---

The problem

I'm trying to implement a date filter using the Laravel JSON:API package with support for range queries like:

&filter[date][gte]=2022-11-30T23:00:00.000Z
&filter[date][lte]=2022-12-31T23:00:00.000Z

And optionally also support a default filter version like:

&filter[date]=2022-11-30T23:00:00.000Z

What I tried

According to the docs (link), I attempted the following in my schema:

Where::make('date'),
Where::make('date')->gte(),
Where::make('date')->lte(),
Where::make('date')->gte()->lte() // Alternative also not working

Unfortunately, none of these approaches work.

What happens

When I try the range query:

&filter[date][gte]=2022-11-30T23:00:00.000Z&filter[date][lte]=2022-12-31T23:00:00.000Z

The generated SQL ends up looking like this:

where
  `table`.`date` = '2022-11-30T23:00:00.000Z'
  and `table`.`date` >= '2022-11-30T23:00:00.000Z'
  and `table`.`date` <= '2022-11-30T23:00:00.000Z'

This isn’t what I expect, as it seems the first value (filter[date][gte]) overrides the rest. even when [date] filter is not given in the url.

Workaround

I created a custom filter that handles this correctly:

CustomBetweenFilter::make('date')

The logic:

foreach ($value as $operator => $filterValue) {
    switch ($operator) {
        case 'gte':
            $query->where($this->field, '>=', $filterValue);
            break;
        case 'lte':
            $query->where($this->field, '<=', $filterValue);
            break;
        case 'gt':
            $query->where($this->field, '>', $filterValue);
            break;
        case 'lt':
            $query->where($this->field, '<', $filterValue);
            break;
        default:
            $query->where($this->field, '=', $filterValue);
            break;
    }
}

Question

Is this a limitation of the Laravel JSON:API package, or is there a recommended way to implement this kind of range filtering using the built-in filters?

Thanks in advance! :)

---

[lindyhopchris]

Hi. You've misunderstood the what gte() etc do. By default the Where filter uses =, all gte() does is change it so that it uses >= instead of =.

You are trying to support an array, that can have a key of gte. That's not what the Where class supports.

What you're trying to do is a good approach, but you'll need to implement your own filter to do that. The implementation allows you to do that, docs on writing a filter are here:
https://laraveljsonapi.io/5.x/schemas/filters.html#writing-filters