Column Filters Api

This section lists all the methods in the column filters class in detail.

$columnFilters->clear()

Resets the values of variables belonging to the ColumnFilters class.

$columnFilters->setSelect(SqlInterface $select)

If filtering is performed between two dates, the $select object should be sent to the columnFilters class. Otherwise, filtering will not occur.

$this->columnFilters->setSelect($this->select);
$this->columnFilters->setDateFilter('creationDate');

$columnFilters->setColumns(array $columns)

Determines which columns should be queried.

$this->columnFilters->setColumns(
    [
        'customerId',
        'customerShortName',
    ]
);

$columnFilters->unsetColumns(array $columns)

Deletes the columns that need to be queried.

$columnFilters->getColumns(): array

Returns the queried column names.

$columnFilters->setAlias(string $name, string $alias)

$this->columnFilters->setAlias('purchaserName', $this->concatFunction);

You can also use Laminas\Db\Sql\Expression object as value.

$this->columnFilters->setAlias('orderItems', new Expression($this->orderItemFunction, [LANG_ID]));

Searching Json Encoded Values

In this scenario, let's assume that we encoded the user roles as an array with JSON_ENCODE and let's perform a filtering within this array. For the roles, create a $this->rolesFunction function as follows in the findAllBySelect method of your UserModel.php file.

public function findAllBySelect()
{
    $platform = $this->adapter->getPlatform();
    $roles = "JSON_ARRAYAGG(";
    $roles.= "JSON_OBJECT(";
    $roles.= "'id' , r.roleId , ";
    $roles.= "'name' , r.roleName ";
    $roles.= "))";
    $this->rolesFunction = $platform->quoteIdentifierInFragment(
        "(SELECT $roles FROM userRoles ur LEFT JOIN roles r ON r.roleId = ur.roleId WHERE ur.userId = u.userId)",
        [
            '(',
            ')',
            '/',
            'u',
            'ur',
            'roleId',
            'userId',
            'id',
            'name',
            'SELECT',
            'FROM',
            'AS',
            ',',
            '[',
            ']',
            'JSON_ARRAYAGG',
            'JSON_OBJECT',
            'WHERE',
            'ORDER',
            'BY',
            'ASC',
            ';',
            'CONCAT',
            '"',
            '\'',
            '\"', '=', '?', 'JOIN', 'ON', 'AND', 'LEFT', ','
        ]
    );
    $sql = new Sql($this->adapter);
    $select = $sql->select();
    $select->columns([
        'id' => 'userId',
        'firstname',
        'lastname',
        'email',
        'active',
        'createdAt',
        'userRoles' => new Expression($this->rolesFunction),            
    ]);
    $select->from(['u' => 'users']);
    return $select;
}

The $this->rolesFunction method should be placed inside the setAlias method as follows and the userRoles should be called inside the setLikeColumns method.

public function findAllByPaging(array $get) : Paginator
{
    $select = $this->findAllBySelect();
    $this->columnFilters->clear();
    $this->columnFilters->setAlias('userRoles', new Expression($this->rolesFunction));
    $this->columnFilters->setColumns([
        'firstname',
        'lastname',
        'email',
        'userRoles',
        'active',
    ]);
    $this->columnFilters->setLikeColumns(
        [
            'firstname',
            'lastname',
            'userRoles',
            'email',
        ]
    );
    $this->columnFilters->setWhereColumns(
        [
            'active',
        ]
    );
    $this->columnFilters->setSelect($select);
    $this->columnFilters->setData($get);

    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);
            }
        }
    }
    // date filters
    // 
    $this->columnFilters->setDateFilter('createdAt');
    // orders
    // 
    if ($this->columnFilters->orderDataIsNotEmpty()) {
        foreach ($this->columnFilters->getOrderData() as $order) {
            $select->order($order);
        }
    }
    // echo $select->getSqlString($this->adapter->getPlatform());
    // die;
    $paginatorAdapter = new DbSelect(
        $select,
        $this->adapter
    );
    return new Paginator($paginatorAdapter);
}

$columnFilters->setLikeColumns($columns)

It allows a like search to be performed within the columns you define.

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

$columnFilters->setWhereColumns($columns)

It allows a where search to be performed within the columns you define.

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

$columnFilters->setGroupedColumns(string $name, array $columns, callable $returnFunc = null)

You will need this function when you convert filters, which usually have more than one boolean type and look messy, into a multi-selection input.

Considering the example above; Let's say you have 3 columns named ssl, verified and resourceAccess and we want to group these columns under a name that does not exist in your table.

$this->columnFilters->setGroupedColumns(
    'resources', // group name 
    [
        'ssl',  // column names that you want to group
        'verified',
        'resourceAccess',
    ]
);

Sql output:

SELECT
  `d`.`domainId` AS `id`,
  `d`.`name` AS `name`,
  `d`.`url` AS `url`,
  `d`.`ssl` AS `ssl`,
  `d`.`verified` AS `verified`,
  `d`.`resourceAccess` AS `resourceAccess`,
FROM
  `domains` AS `d`
WHERE `ssl` = '1'
  AND `verified` = '1'
  AND `resourceAccess` = '1'

If we had chosen to send a closure function from the 3rd parameter, the result would be as follows.

$this->columnFilters->setGroupedColumns(
    'resources', // group name 
    [
        'ssl',  // column names that you want to group
        'verified',
        'resourceAccess',
    ],
    function ($val) {
        return (string)$val;
    }
);

Sql output:

SELECT
  `d`.`domainId` AS `id`,
  `d`.`name` AS `name`,
  `d`.`url` AS `url`,
  `d`.`ssl` AS `ssl`,
  `d`.`verified` AS `verified`,
  `d`.`resourceAccess` AS `resourceAccess`,
FROM
  `domains` AS `d`
WHERE `ssl` = 'ssl'
  AND `verified` = 'verified'
  AND `resourceAccess` = 'resourceAccess'

$columnFilters->getRawData(): array

Returns column data that was not processed by the column filter.

$columnFilters->setData(array $data)

It sends the incoming http data in array format to the column filter class.

$this->columnFilters->setData($get);

$columnFilters->getData() : array

It returns incoming http data in array format.

$columnFilters->setDateFilter(string $dateColumn, $endDate = null, $fixedDate = null)

It automatically creates date filters based on the entered values.

Filter by a Single Column

Considering the example below, if the endDate value is sent empty,

$this->columnFilters->setSelect($this->select);
$this->columnFilters->setDateFilter('creationDate');

This code produces a SQL query as follows.

/*
$nest = $this->select->where->nest();
    $nest->and->between($dateColumn, $data[$columnStart], $data[$columnEnd]);
$nest->unnest();
*/

Query Between Two Columns

If you only need to query between two specific database columns, the 2nd parameter makes the query occur between two dates and,

$this->columnFilters->setSelect($this->select);
$this->columnFilters->setDateFilter('startDate', 'endDate');

The above code produces an SQL query as follows.

/*
$nest = $this->select->where->nest();
    $nest->and->lessThanOrEqualTo($columnStart, $data[$endKey])
         ->and->greaterThanOrEqualTo($columnEnd, $data[$startKey]);
$nest->unnest();
*/

Query Based on a Fixed Date

If fixedDate value is entered,

$this->columnFilters->setSelect($this->select);
$this->columnFilters->setDateFilter('startDate', 'endDate', 'startDate');

This code produces an SQL query as follows.

/*
$nest = $this->select->where->nest();
    $nest->and->lessThanOrEqualTo($columnStart, $data[$fixedDate])
         ->and->greaterThanOrEqualTo($columnEnd, $data[$fixedDate]);
$nest->unnest();
*/

$columnFilters->getLikeData(): array

If a search request is sent by columns, the queried column names and values are returned.

print_r($this->columnFilters->getLikeData());
die;
/*
Array
(
    [`firstname`] => demo
)
*/

$columnFilters->getWhereData(): array

If a search request is sent by columns, the queried column names and values are returned.

print_r($this->columnFilters->getWhereData());
die;
/*
Array
(
    [c.companyId] => Array
        (
            [0] => ebf6b935-5bd8-46c1-877b-9c758073f278
        )

    [j.jobTitleId] => Array
        (
            [0] => 137b00c8-0e36-ce3a-25f2-ce4b7b1cf97c
        )

    [g.gradeId] => Array
        (
            [0] => 8e9204c4-0133-4a51-82ca-4265b1656b1d
            [1] => 07ef35ed-5f96-4776-a57a-998d5f09a891
        )
)
*/

$columnFilters->getOrderData() : array

Returns sorted column names and values.

print_r($this->columnFilters->getOrderData());
die;
/*
Array
(
    [0] => j.jobTitleId ASC
    [1] => name ASC
)
*/

$columnFilters->getSearchData()

If search values are sent from the global search entry, the text search returns the column names and values.

print_r($this->columnFilters->getSearchData());
die;
/*
Array
(
    [c.companyId] => Array
        (
            [0] => Brown
        )

    [`employeeNumber`] => Array
        (
            [0] => Brown
        )

    [`name`] => Array
        (
            [0] => Brown
        )

    [`surname`] => Array
        (
            [0] => Brown
        )

    [j.jobTitleId] => Array
        (
            [0] => Brown
        )

    [g.gradeId] => Array
        (
            [0] => Brown
        )
)
*/

$columnFilters->searchDataIsNotEmpty()

Returns true if the "global search" data is not empty, otherwise false.

$columnFilters->searchDataEmpty()

If the "global search" data is empty, it returns true, otherwise it returns false.

$columnFilters->likeDataIsNotEmpty()

If the "like" filter data is not empty, it returns true, otherwise it returns false.

$columnFilters->likeDataIsEmpty()

If the "like" filter data is empty, it returns true, otherwise it returns false.

$columnFilters->whereDataIsNotEmpty()

If the "where" filter data is not empty, it returns true, otherwise it returns false.

$columnFilters->whereDataIsEmpty()

If the "where" filter data is empty, it returns true, otherwise false.

$columnFilters->orderDataIsNotEmpty()

Returns true if the "sorting" data is not empty, otherwise false.

$columnFilters->orderDataIsEmpty()

If the "sorting" data is empty, it returns true, otherwise it returns false.