Add option to match "NULL", "starting with" and "ending with" to filters and others

[Vinai]

Message in Slack from Wouter Samaey:

One genius improvement hyva-admin could bring is more flexible filters, like filter a column to only show NULL values or "starting with", "end with", "multiple select values with multiple values being required or 1 of many required". We could keep the current presentation and functioanlity, but add a dropdown angle after the filter input that allows you to select another filter type. Better filters, means better mass selections and greater productivity. Something that's been lacking in Magento since day 1.

[woutersamaey]

Even a basic MQL text field would work for power users. With MQL I mean "Magento Query Language", kind of like Atlassian's JIRA has JQL. It's a bit like SQL. If you know the fields and basic syntax, you can write your own search. https://www.atlassian.com/blog/jira-software/jql-the-most-flexible-way-to-search-jira-14

This too would improve search a lot, but for power users only. Some may find it too complex, others would be able to build any type of query not easily expressed in a GUI (like with nested AND and OR criteria).

Every criteria (separated by AND) would simply add a ->addFieldToFilter() fragment to the collection.

[Vinai]

Internally Hyva_Admin grids uses Magento's SearchCriteria for sorting, paging and filtering. As long as a filter value can be expressed with the SearchCriteria feature set it will work.

• The SearchCriteria is passed straight to the repository gird source types.
• For collections, it is translated into addFieldToFilter calls by the \Magento\Framework\Api\SearchCriteria\CollectionProcessor\FilterProcessor
• For query source types it is translated into SQL conditions by \Magento\Framework\DB\Adapter\AdapterInterface::prepareSqlCondition
• For array collections it is applied by the array source type itself (see \Hyva\Admin\Model\GridSourceType\ArrayProviderGridSourceType::isMatchingFilter)

Thus it's best not to think on the abstraction level of collection filters but rather on the FilterGroups and Filtersof SearchCriteria...

[woutersamaey]

Good call. I haven't looked at the code yet. Just wanted to point out how "pragmatic" it could be implemented. The solution may be closer than one would think.

[Vinai]

Here is the list of supported conditions and. the SQL they would translate to:

$conditionKeyMap = [
            'eq'            => "{{fieldName}} = ?",
            'neq'           => "{{fieldName}} != ?",
            'like'          => "{{fieldName}} LIKE ?",
            'nlike'         => "{{fieldName}} NOT LIKE ?",
            'in'            => "{{fieldName}} IN(?)",
            'nin'           => "{{fieldName}} NOT IN(?)",
            'is'            => "{{fieldName}} IS ?",
            'notnull'       => "{{fieldName}} IS NOT NULL",
            'null'          => "{{fieldName}} IS NULL",
            'gt'            => "{{fieldName}} > ?",
            'lt'            => "{{fieldName}} < ?",
            'gteq'          => "{{fieldName}} >= ?",
            'lteq'          => "{{fieldName}} <= ?",
            'finset'        => "FIND_IN_SET(?, {{fieldName}})",
            'nfinset'       => "NOT FIND_IN_SET(?, {{fieldName}})",
            'from'          => "{{fieldName}} >= ?",
            'to'            => "{{fieldName}} <= ?",
        ];
        ```