Codeigniter parentheses in Active Record query

Question

I have this ActiveRecord to produce a query,

$this->purchase_requisition_model
  ->where('deleted','1')
  ->likes('to',$sapfvalue,'both')
  ->likes('date',$sapfvalue,'both')
  ->likes('request_by',$sapfvalue,'both')
  ->likes('deliver_to',$sapfvalue,'both')
  ->likes('name',$sapfvalue,'both')
   ->likes('telephone',$sapfvalue,'both')
  ->likes('designation',$sapfvalue,'both')
  ->likes('budget_status',$sapfvalue,'both')
  ->find_all();

the above ActiveRecord will produce the following query,

SELECT  * FROM (`purchase_requisition`)
WHERE `deleted` =  '1'
AND  `to`  LIKE '%fg%'
OR  `date`  LIKE '%fg%'
OR  `request_by`  LIKE '%fg%'
OR  `deliver_to`  LIKE '%fg%'
OR  `name`  LIKE '%fg%'
OR  `telephone`  LIKE '%fg%'
OR  `designation`  LIKE '%fg%'
OR  `budget_status`  LIKE '%fg%'

but how do I do using ActiveRecord to produce the below query?

SELECT  * FROM (`purchase_requisition`)
WHERE `deleted` =  '1'
AND  (  `to`  LIKE '%fg%'
OR  `date`  LIKE '%fg%'
OR  `request_by`  LIKE '%fg%'
OR  `deliver_to`  LIKE '%fg%'
OR  `name`  LIKE '%fg%'
OR  `telephone`  LIKE '%fg%'
OR  `designation`  LIKE '%fg%'
OR  `budget_status`  LIKE '%fg%' )

According to [docs](http://ellislab.com/codeigniter/user-guide/database/active_record.html) there is no **likes()** function may be you are using any cms based on codeigniter if so then tag it properly you are not directly using active record,also grouped where clauses are not yet supported by active record you need to write raw query with proper escaping of inputs — M Khalid Junaid, May 08 '14 at 16:25

score 3 · Answer 1 · edited May 23 '17 at 12:15

As @M Khalid Junaid has pointed out, Codeigniter's active record library doesn't support grouped where clauses. You can create a workaround by using "where" while preventing Codeigniter from automatically escaping the query:

$escaped_sapfvalue = $this->db->escape( $sapfvalue );
$this->purchase_requisition_model
->where('deleted','1')
->where("( `to` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->where("OR `date` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->where("OR `request_by` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->where("OR `deliver_to` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->where("OR `name` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->where("OR `telephone` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->where("OR `designation` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->where("OR `budget_status` LIKE '%{$escaped_sapfvalue}%' )", null, FALSE)
->find_all();

Notice than how I manually escaped the variable to prevent SQL injection. Also notice how the first and last "LIKE" include the opening and closing parenthesis, respectively.

While this works, it has a lot of repeated code. Looping through an array would be more elegant:

$escaped_sapfvalue = $this->db->escape( $sapfvalue );
$or_like = '';

foreach( $column_list as $column ) {
    // If it's not the first column, add 'OR'
    if ( strlen($or_like) > 0 ) {
        $or_like .= ' OR ';
    }
    // Concatenate manually escaped columns and rows
    $escaped_column = $this->db->escape( $column );
    $or_like .= "`{$escaped_column}` LIKE '%{$escaped_sapfvalue}%'";
}

// Add grouping parenthesis
$grouped_or_like = "( {$or_like} )";

// Build the query
$this->purchase_requisition_model
->where('deleted','1')
->where( $grouped_or_like, null, false )
->find_all();

EDIT: And while I haven't tested it, I've just thought this should work too:

$escaped_sapfvalue = $this->db->escape( $sapfvalue );
$this->purchase_requisition_model
->where('deleted','1')
->where("( `to` LIKE '%{$escaped_sapfvalue}%'", null, FALSE)
->or_like('date',$sapfvalue,'both')
->or_like('request_by',$sapfvalue,'both')
->or_like('deliver_to',$sapfvalue,'both')
->or_like('name',$sapfvalue,'both')
->or_like('telephone',$sapfvalue,'both')
->or_like('designation',$sapfvalue,'both')
->where("OR `budget_status` LIKE '%{$escaped_sapfvalue}%' )", null, FALSE)
->find_all();

Choose whatever works best for you.

Thanks for that I am doing a loop on search terms against all columns defined in an array your examples will help me build a query which will allow me to enforce a where condition like "active = true" while allowing me to search by text against any columns in the array. — Someone, Sep 29 '15 at 07:35

score 0 · Answer 2 · answered Dec 06 '18 at 08:09

You can use query grouping too.

Code will look like this:

$this->db->where('deleted', 1);
$this->db->group_start();
$this->db->or_like('to', $sapfvalue, 'both')
$this->db->or_like('date', $sapfvalue, 'both')
$this->db->or_like('request_by', $sapfvalue,  'both')
$this->db->or_like('deliver_to', $sapfvalue, 'both')
$this->db->or_like('name', $sapfvalue, 'both')
$this->db->or_like('telephone', $sapfvalue, 'both')
$this->db->or_like('designation', $sapfvalue, 'both')
$this->db->or_like('budget_status', $sapfvalue, 'both')
$this->db->group_end();

$q = $this->db->get('table')->result();

Using group_start and group_end assures that this part of or_like statements between then will get into brackets.

Codeigniter parentheses in Active Record query

2 Answers2