Build Complex LIKE Query with CakePHP Query Builder

Written by James McDonald

April 30, 2022

How to create this MySQL query with CakePHP 4?

SET @search = '9233' COLLATE utf8mb4_general_ci;

SELECT 
    *
FROM
    100pbc_new.addresses
WHERE
    trading_partner LIKE CONCAT('%', @search, '%')
        OR dc_code LIKE CONCAT('%', @search, '%')
        OR dc_name LIKE CONCAT('%', @search, '%')
        OR tpc_code LIKE CONCAT('%', @search, '%')
        OR edi_number LIKE CONCAT('%', @search, '%')
        OR address_1 LIKE CONCAT('%', @search, '%')
        OR address_2 LIKE CONCAT('%', @search, '%')
        OR address_3 LIKE CONCAT('%', @search, '%')
        OR city LIKE CONCAT('%', @search, '%')
        OR state LIKE CONCAT('%', @search, '%')
        OR post_code LIKE CONCAT('%', @search, '%')
        OR bookings_telephone LIKE CONCAT('%', @search, '%')
        OR receiving_telephone LIKE CONCAT('%', @search, '%')
        OR trading_partner_id LIKE CONCAT('%', @search, '%');

Controller Action

 public function columns($search = null)
    {
        $cols = new Collection($this->Addresses->getSchema()->columns());

        $searchColumns = $cols->filter(function ($col) {
            return $this->Addresses->getSchema()->getColumn($col)['type'] === 'string';
        })->toArray();

        $conditions = [];
        foreach ($searchColumns as $col) {
            $conditions['OR'][$col . ' LIKE'] = '%' . $search . '%';
        }

        $matches = $this->Addresses->find()
            ->where($conditions);

        dd($matches->toArray());
    }

0 Comments

Submit a Comment

Your email address will not be published.

You May Also Like…

List your VSCode Extensions

Ever wondered what extensions you have installed and want to keep a list? This actually is a good way to audit your...

array_merge vs the + operator

<?php $options = [ 'rootNode' => 'response' ]; // array_merge // the same key appearing later will overwrite echo...