动态 Active Record 查询中的 Codeigniter 括号

2021-11-18 00:00:00 arrays mysql codeigniter activerecord

我正在使用 ActiveRecord 生成如下查询

I'm producing a query like the following using ActiveRecord

SELECT * FROM (`foods`) WHERE `type` = 'fruits' AND 
       `tags` LIKE '%green%' OR `tags` LIKE '%blue%' OR `tags` LIKE '%red%'

tags 的数量和值未知.数组是动态创建的.下面我添加了一个可能的数组.

The number of tags and values is unknown. Arrays are created dynamically. Below I added a possible array.

$tags = array (                 
        '0'     => 'green'.
        '1'     => 'blue',
        '2'     => 'red'
);

有一个标签数组，我使用以下循环来创建我在上面发布的查询.

Having an array of tags, I use the following loop to create the query I posted on top.

$this->db->where('type', $type); //var type is retrieved from input value

foreach($tags as $tag):         
     $this->db->or_like('tags', $tag);
endforeach;

问题:我需要在 LIKE 子句周围添加括号，如下所示:

The issue: I need to add parentheses around the LIKE clauses like below:

SELECT * FROM (`foods`) WHERE `type` = 'fruits' AND 
      (`tags` LIKE '%green%' OR `tags` LIKE '%blue%' OR `tags` LIKE '%red%')

如果括号内的内容是静态的，但 foreach 循环让我失望，我知道如何完成此操作..

I know how to accomplish this if the content within the parentheses was static but the foreach loop throws me off..

推荐答案

来自 CI wiki:

From the CI wiki:

codeignighter ActiveRecord 功能允许您创建 SQL 查询相对简单和独立于数据库，但是有没有特别支持包括SQL 查询中的括号.

The codeignighter ActiveRecord feature allows you to create SQL queries relatively simply and database-independant, however there isno specific support for including parenthesis in an SQL query.

例如，当您希望 where 语句与以下内容类似时:

For example when you want a where statement to come out simmilarly to the folowing:

WHERE (field1 = value || field2 = value) AND (field3 = value2 || field4 = value2)

这可以通过向 CI->db->where() 函数提供字符串来解决，在这种情况下，您需要专门转义您的值.

This can be worked around by feeding a string to the CI->db->where() function, in this case you will want to specifically escape your values.

看下面的例子:

$value=$this->db->escape($value);
$value2=$this->db->escape($value2);
$this->db->from('sometable');
$this->db->where("($field = $value || $field2 = $value)");
$this->db->where("($field3 = $value2 || $field4 = $value2)");
$this->db->get();

类似的解决方法可用于 LIKE 子句:

A simmilar workaround can be used for LIKE clauses:

$this->db->where("($field LIKE '%$value%' || $field2 LIKE '%$value%')");
$this->db->where("($field3 LIKE '%$value2%' || $field4 LIKE '%$value2%')");

相关文章