CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordbonfire
sulaiman sudirman
sulaiman sudirman

Reputation: 1845

Codeigniter parentheses in Active Record query

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

Upvotes: 3

Views: 1074

Answers (2)

michal.jakubeczy
michal.jakubeczy

Reputation: 9469

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.

Upvotes: 0

luis_pmb
luis_pmb

Reputation: 146

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.

Upvotes: 3

Related Questions