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…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...