Why is the `LIKE` operator not working with integer columns?

I’m trying to receive some ids from my database for an autocomplete search on my CAKEPHP 3.3 site. But my problem is that its only returning the id if I type in the exact id and not part of it.

Here is my function to search the data. The name variable is what is being passed from input.

public function search()
{
    if ($this->request->is('ajax')) 
    {
        $name = $this->request->query['term'];
        $resultArr = $this->Invoices->find('all', [
            'conditions' => ['Invoices.id LIKE' => ($name . '%')]
        ]);
        $resultsArr = [];
        foreach ($resultArr as $result) 
        {
             $resultsArr[] = ($result['id']);
        }

        $this->set('resultsArr', $resultsArr);
        // This line is what handles converting your array into json
        // To get this to work you must load the request handler
        $this->set('_serialize', ['resultsArr']);
    }
}

​x
 
public function search(){    if ($this->request->is('ajax'))     {        $name = $this->request->query['term'];        $resultArr = $this->Invoices->find('all', [            'conditions' => ['Invoices.id LIKE' => ($name . '%')]        ]);        $resultsArr = [];        foreach ($resultArr as $result)         {             $resultsArr[] = ($result['id']);        }​        $this->set('resultsArr', $resultsArr);        // This line is what handles converting your array into json        // To get this to work you must load the request handler        $this->set('_serialize', ['resultsArr']);    }}​

For example there is a id in the table ‘5254’ and I type in part of the id ’52’ nothing is returned but when I type in the whole id ‘5254’ the id is returned.

I’m unsure why this is the case because in my sql query i’m using the percent sign to say any characters after what has been typed into the input.

Here is part of my table

SQL debug when 52 is entered.

object(CakeORMQuery) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'SELECT Invoices.id AS `Invoices__id`, Invoices.start_date AS `Invoices__start_date`, Invoices.close_date AS `Invoices__close_date`, Invoices.customer_id AS `Invoices__customer_id`, Invoices.invoice_to_address AS `Invoices__invoice_to_address`, Invoices.ship_to_address AS `Invoices__ship_to_address`, Invoices.customer_contact_id AS `Invoices__customer_contact_id`, Invoices.aircraft_registration_id AS `Invoices__aircraft_registration_id`, Invoices.shipping_company_id AS `Invoices__shipping_company_id`, Invoices.notes AS `Invoices__notes`, Invoices.worksheet_notes AS `Invoices__worksheet_notes`, Invoices.closed AS `Invoices__closed`, Invoices.times_printed AS `Invoices__times_printed`, Invoices.payment_due AS `Invoices__payment_due`, Invoices.GST_rate AS `Invoices__GST_rate`, Invoices.opening_notes AS `Invoices__opening_notes`, Invoices.courier_ticket AS `Invoices__courier_ticket`, Invoices.job_description AS `Invoices__job_description`, Invoices.worksheets_printed AS `Invoices__worksheets_printed`, Invoices.supervising_engineer_id AS `Invoices__supervising_engineer_id`, Invoices.job_type_id AS `Invoices__job_type_id`, Invoices.opened_by_id AS `Invoices__opened_by_id`, Invoices.assigned_to_id AS `Invoices__assigned_to_id`, Invoices.certification_required AS `Invoices__certification_required`, Invoices.currency_id AS `Invoices__currency_id`, Invoices.xero_batch_number AS `Invoices__xero_batch_number`, Invoices.xero_amount AS `Invoices__xero_amount`, Invoices.exchange_rate AS `Invoices__exchange_rate`, Invoices.payment_instructions AS `Invoices__payment_instructions`, Invoices.email AS `Invoices__email`, Invoices.inv_email AS `Invoices__inv_email` FROM invoices Invoices WHERE Invoices.id like :c0',
    'params' => [
        ':c0' => [
            'value' => '52%',
            'type' => 'integer',
            'placeholder' => 'c0'
        ]

 
object(CakeORMQuery) {​    '(help)' => 'This is a Query object, to get the results execute or iterate it.',    'sql' => 'SELECT Invoices.id AS `Invoices__id`, Invoices.start_date AS `Invoices__start_date`, Invoices.close_date AS `Invoices__close_date`, Invoices.customer_id AS `Invoices__customer_id`, Invoices.invoice_to_address AS `Invoices__invoice_to_address`, Invoices.ship_to_address AS `Invoices__ship_to_address`, Invoices.customer_contact_id AS `Invoices__customer_contact_id`, Invoices.aircraft_registration_id AS `Invoices__aircraft_registration_id`, Invoices.shipping_company_id AS `Invoices__shipping_company_id`, Invoices.notes AS `Invoices__notes`, Invoices.worksheet_notes AS `Invoices__worksheet_notes`, Invoices.closed AS `Invoices__closed`, Invoices.times_printed AS `Invoices__times_printed`, Invoices.payment_due AS `Invoices__payment_due`, Invoices.GST_rate AS `Invoices__GST_rate`, Invoices.opening_notes AS `Invoices__opening_notes`, Invoices.courier_ticket AS `Invoices__courier_ticket`, Invoices.job_description AS `Invoices__job_description`, Invoices.worksheets_printed AS `Invoices__worksheets_printed`, Invoices.supervising_engineer_id AS `Invoices__supervising_engineer_id`, Invoices.job_type_id AS `Invoices__job_type_id`, Invoices.opened_by_id AS `Invoices__opened_by_id`, Invoices.assigned_to_id AS `Invoices__assigned_to_id`, Invoices.certification_required AS `Invoices__certification_required`, Invoices.currency_id AS `Invoices__currency_id`, Invoices.xero_batch_number AS `Invoices__xero_batch_number`, Invoices.xero_amount AS `Invoices__xero_amount`, Invoices.exchange_rate AS `Invoices__exchange_rate`, Invoices.payment_instructions AS `Invoices__payment_instructions`, Invoices.email AS `Invoices__email`, Invoices.inv_email AS `Invoices__inv_email` FROM invoices Invoices WHERE Invoices.id like :c0',    'params' => [        ':c0' => [            'value' => '52%',            'type' => 'integer',            'placeholder' => 'c0'        ]​

Answer

The id column is of type INTEGER, and therefore the value is being bound as such, as can be seen in your Query dump, it says 'type' => 'integer'. Being bound as an integer will cause it to be casted, and you’ll end up with a comparison against 52 only.

You can workaround that by telling the query builder to treat the column as a string type. This can be done via the second argument ($types) of the query builders *where() methods:

$this->Invoices
    ->find()
    ->where(
        ['Invoices.id LIKE' => ($name . '%')],
        ['Invoices.id' => 'string']
    );

 
$this->Invoices    ->find()    ->where(        ['Invoices.id LIKE' => ($name . '%')],        ['Invoices.id' => 'string']    );​

Advertisement

Answer