为什么‘`LIKE`’运算符不能处理整数列？

Question

我正试图从我的数据库中接收一些ids，以便在我的CAKEPH3.3站点上进行自动完成搜索。但是我的问题是，如果我输入确切的id而不是它的一部分，它只返回id。

这是我搜索数据的函数。名称变量是从输入传递的内容。

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']);
    }
}

例如，表'5254‘中有一个id，我键入了id '52’的一部分，没有返回任何内容，但是当我输入整个id '5254‘时，id就返回了。

​

​

​

我不知道为什么会出现这种情况，因为在我的sql查询中，我在输入输入后使用百分比符号来表示任何字符。

这是我桌子的一部分

​

输入52时进行SQL调试。

object(Cake\ORM\Query) {

    '(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 1

id列的类型是INTEGER，因此该值是按这种方式绑定的，在查询转储中可以看到，它表示为'type' => 'integer'。将其绑定为整数将导致它被抛出，最后只会与52进行比较。

您可以通过告诉查询生成器将列视为字符串类型来解决这一问题。这可以通过查询生成器$types方法的第二个参数( *where() )来完成：

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

另请参阅

• API > \Cake\ORM\Query::where()

Answer 2

试着这样做：

'conditions' => ['Invoices.id LIKE' => '"' . $name . '%"']

Answer 3

在这种情况下，您可以“注入”普通查询-- conditions中带有数字索引的数组值被视为普通查询，而且它不会被参数化。小心:为了防止SQL注入，必须对整数进行类型转换：

    $result = $this->Invoinces->find('all' , [
            'conditions' => [
                'id LIKE "'.(int)$input.'%" '
            ]
        ])
    ->toArray();