Codeigniter parentheses in dynamic Active Record query

user_468312 0
Viewed 17978

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%'

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;

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%')

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

codeigniter activerecord mysql arrays
9 Answers

use codeigniter 3

$this->db->select('*');
    $this->db->from($this->MasterMember);
    $this->db->group_start();
    $this->db->where($this->IDCardStatus, '1');
    $this->db->or_where($this->IDCardStatus, '2');
    $this->db->group_end();

    if ($searchKey1 != null) {
        $this->db->group_start();
        $this->db->like($this->MemberID, $searchKey1);
        $this->db->or_like($this->FirstName, $searchKey2);
        $this->db->or_like($this->LastName, $searchKey3);
        $this->db->group_end();
    }

    $this->db->limit($limit, $offset);


    $data = $this->db->get();

this is my native query

SELECT
    * 
FROM
    `Member` 
WHERE ( `Member`.`IDCardStatus` = '1' OR `Member`.`IDCardStatus` = '2' ) 
AND ( `Member`.`MemberID` LIKE '%some_key%' ESCAPE '!' OR `Member`.`FirstName` LIKE '%some_key%' ESCAPE '!' OR `Member`.`LastName` LIKE '%some_key%' ESCAPE '!' ) 
    LIMIT 10
user_22337640

Update for codeigniter 4:

$builder->select('*')->from('my_table')
        ->groupStart()
            ->where('a', 'a')
            ->orGroupStart()
                    ->where('b', 'b')
                    ->where('c', 'c')
            ->groupEnd()
    ->groupEnd()
    ->where('d', 'd')
->get();

// Generates:
// SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd'

from official docs on: https://codeigniter.com/user_guide/database/query_builder.html#query-grouping

user_23877410
Related