Logical "or" filter doesn't work with most operators

[binary-koan]

Bug Report

Note: Once again I'm not sure whether this is a bug report or feature request. Let me know if I should reclassify it.

Steps to Reproduce

My actual use case: Retrieve promotions which have not yet expired, i.e. expires_at is null OR is a time greater than now.

While trying to make this work, I discovered a bit of a can of worms with logical=or not working for various operator types (regardless of whether you use multiple filters on the same field or multiple fields) ...

1. Set up a collection with a nullable expires_at field (just because it's easiest to see the problem if the query is of the form "null OR something")
2. Run the following query with any of the operators listed below:

   /items/COLLECTION?filter[expires_at][0][null]&filter[expires_at][1][logical]=or&filter[expires_at][1][OPERATOR]=VALUE
   

Operators which don't work: neq, lt, lte, gt, gte, nin, null, nnull, ncontains, nrlike, nbetween
Operators which do work: eq, in, contains, rlike, between

Expected Behavior

The API returns all items where expires_at is null OR matches the given condition.

Actual Behavior

For the list of operators which don't work, the API ignores the or, uses and instead, and therefore returns an empty list.

Other Context & Screenshots

It looks like this is because the whereXXX methods in src\core\Directus\Database\Query\Builder.php take inconsistent arguments - the operators which work take something like ($column, $value, $not = false, $logical = 'and') and most of the ones which don't just take ($column, $value). The exception is whereNull, which has $logical as the third parameter instead of the fourth.

When RelationTableGateway::doFilter calls those methods on Builder, it assumes all the methods take 4 arguments in the same order.

As an experiment, I tried updating whereGreaterThan to:

    public function whereGreaterThan($column, $value, $not = false, $logical = 'and')
    {
        return $this->where($column, '>', $value, $not, $logical);
    }

and the query filter[expires_at][0][null]&filter[expires_at][1][logical]=or&filter[expires_at][1][>]=0 correctly returned all documents.

Technical Details

• Device: Desktop
• OS: WSL Ubuntu 18.04 on Windows 10
• Web Server: Apache/2.4.29 (Ubuntu)
• PHP Version: 7.2.10-0ubuntu0.18.04.1
• Database: Ver 14.14 Distrib 5.7.24, for Linux (x86_64)
• Install Method: Cloned master branch (and pulled just now, just in case)

[theharshin]

Thanks for the report @binary-koan. @hemratna is building a GraphQL implementation and he is also facing this issue. We'll have a fix for this soon 🙂

[binal-7span]

Closed in #908