Column Filters

If you have a grid filters created with Data Table Server or Data Iterator components, the column filter class in the backend, Olobase\Mezzio\ColumnFilters parses the URL address sent to the API from the frontend, runs it in the database, and creates SQL queries for filtering.

The filtering seen in the image above will appear in your browser and backend API as follows:

http://127.0.0.1:3000/employees?page=1&perPage=10&filter={"jobTitleId":[{"id":"137b00c8-0e36-ce3a-25f2-ce4b7b1cf97c","name":"Web+Designer"},{"id":"28fd1a31-becf-2329-6bcf-0c80bcc64e2d","name":"Computer+Programmer"}],"companyId":[{"id":"ebf6b935-5bd8-46c1-877b-9c758073f278","name":"Demo+Company","companyShortName":"Demo"}]}

http://demo.local/api/employees/findAllByPaging?jobTitleId[][id]=137b00c8-0e36-ce3a-25f2-ce4b7b1cf97c&jobTitleId[][name]=Web Designer&jobTitleId[][id]=28fd1a31-becf-2329-6bcf-0c80bcc64e2d&jobTitleId[][name]=Computer Programmer&companyId[][id]=ebf6b935-5bd8-46c1-877b-9c758073f278&companyId[][name]=Demo Company&companyId[][companyShortName]=Demo&_perPage=10&_page=1

The values sent to the backend are obtained as follows.

$get = $request->getQueryParams();
var_dump($get);
/*
array (
  'jobTitleId' => 
  array (
    0 => array ('id' => '137b00c8-0e36-ce3a-25f2-ce4b7b1cf97c'),
    1 => array ('name' => 'Web Designer'),
    2 => array ('id' => '28fd1a31-becf-2329-6bcf-0c80bcc64e2d',),
    3 => array ('name' => 'Computer Programmer',),
  ),
  'companyId' => 
  array (
    0 => array ('id' => 'ebf6b935-5bd8-46c1-877b-9c758073f278'),
    1 => array ('name' => 'Demo Company'),
    2 => array ('companyShortName' => 'Demo'),
  ),
  '_perPage' => '10',
  '_page' => '1',
)
*/

The columnFilters->setColumns() method is used to define your existing columns for the column filtering class.

$this->columnFilters->setColumns([
    'companyId',
    'employeeNumber',
    'name',
    'surname',
    'companyId',
    'jobTitleId',
    'gradeId'
]);

If you want to use another table name instead of the column name, the columnFilters->setAlias() method is used.

$this->columnFilters->setAlias('companyId', 'c.companyId');

If you want a like search to occur within the columns you define, you should use the columnFilters->setLikeColumns() method.

$this->columnFilters->setLikeColumns(
    [
        'employeeNumber',
        'name',
        'surname',
    ]
);

If you want a where search to occur within the columns you define, you should use the columnFilters->setWhereColumns() method.

$this->columnFilters->setWhereColumns(
    [
        'companyId',
        'jobTitleId',
        'gradeId',
        'departmentId',
    ]
);

The following example shows an example of column filtering with the findAllByPaging() method of the EmployeeModel class;

src/App/Model/EmployeeModel.php

<?php
declare(strict_types=1);

namespace App\Model;

use Olobase\Mezzio\ColumnFiltersInterface;

class EmployeeModel
{
    public function __construct(
        TableGatewayInterface $employees,
        TableGatewayInterface $employeeChildren,
        TableGatewayInterface $employeeFiles,
        TableGatewayInterface $files,
        ColumnFiltersInterface $columnFilters
    ) {
        $this->adapter = $employees->getAdapter();
        $this->employees = $employees;
        $this->employeeChildren = $employeeChildren;
        $this->employeeFiles = $employeeFiles;
        $this->files = $files;
        $this->conn = $this->adapter->getDriver()->getConnection();
        $this->columnFilters = $columnFilters;
    }
    /*
      ...
      .
    */
    public function findAllByPaging(array $get)
    {
        $select = $this->findAll();
        $this->columnFilters->clear();
        $this->columnFilters->setAlias('companyId', 'c.companyId');
        $this->columnFilters->setAlias('jobTitleId', 'j.jobTitleId');
        $this->columnFilters->setAlias('gradeId', 'g.gradeId');
        $this->columnFilters->setColumns([
            'companyId',
            'employeeNumber',
            'name',
            'surname',
            'companyId',
            'jobTitleId',
            'gradeId'
        ]);
        $this->columnFilters->setLikeColumns(
            [
                'employeeNumber',
                'name',
                'surname',
            ]
        );
        $this->columnFilters->setWhereColumns(
            [
                'companyId',
                'jobTitleId',
                'gradeId',
                'departmentId',
            ]
        );
        $this->columnFilters->setData($get);
        $this->columnFilters->setSelect($select);

        if ($this->columnFilters->searchDataIsNotEmpty()) {
            $nest = $select->where->nest();
            foreach ($this->columnFilters->getSearchData() as $col => $words) {
                $nest = $nest->or->nest();
                foreach ($words as $str) {
                    $nest->or->like(new Expression($col), '%'.$str.'%');
                }
                $nest = $nest->unnest();
            }
            $nest->unnest();
        }
        if ($this->columnFilters->likeDataIsNotEmpty()) {
            foreach ($this->columnFilters->getLikeData() as $column => $value) {
                if (is_array($value)) {
                    $nest = $select->where->nest();
                    foreach ($value as $val) {
                        $nest->or->like(new Expression($column), '%'.$val.'%');
                    }
                    $nest->unnest();
                } else {
                    $select->where->like(new Expression($column), '%'.$value.'%');
                }
            }   
        }
        if ($this->columnFilters->whereDataIsNotEmpty()) {
            foreach ($this->columnFilters->getWhereData() as $column => $value) {
                if (is_array($value)) {
                    $nest = $select->where->nest();
                    foreach ($value as $val) {
                        $nest->or->equalTo(new Expression($column), $val);
                    }
                    $nest->unnest();
                } else {
                    $select->where->equalTo(new Expression($column), $value);
                }
            }
        }
        if ($this->columnFilters->orderDataIsNotEmpty()) {
            $select->order($this->columnFilters->getOrderData());
        }
        // echo $select->getSqlString($this->adapter->getPlatform());
        // die;
        $paginatorAdapter = new DbSelect(
            $select,
            $this->adapter
        );
        $paginator = new Paginator($paginatorAdapter);
        return $paginator;
    }
    /*
      ...
      .
    */
}