Build Complex LIKE Query with CakePHP Query Builder

by | Apr 30, 2022 | IT Tips | 0 comments

How to create this MySQL query with CakePHP 4?

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.