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]));

$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.

Grouped Column Filters

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.

Get Like Data

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.

Get Where Data

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.

Get Where Data

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.