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. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Ubuntu on Hyper-v

It boils town to installing linux-azure # as root or sudo apt-get update apt-get install linux-azure...