CODEWITHSUNDEEP
phpcodeigniteractiverecordquery-builderlogical-grouping
Bugfixer
Bugfixer

Reputation: 2617

Convert SELECT query containing equals comparison and 3 OR LIKE conditions to CodeIgniter active record syntax

I need to translate the following MySQL query to a CodeIgniter active record script.

SELECT *
FROM cdr
WHERE (
    Circle LIKE '%D%'
    OR CLI LIKE '%D%'
    OR Operator LIKE '%D%'
)
AND Dept = 'Sale'

I just want it like $this->db->like('Circle','%D%') with the Dept comparison combined with 'AND'.

Upvotes: 0

Views: 44

Answers (2)

mickmackusa
mickmackusa

Reputation: 47864

Simply feed an associative array to or_like() and encapsulate those conditions in parentheses with group_start() and group_end().

return $this->db
    ->group_start()
        ->or_like(['Circle' => 'D', 'CLI' => 'D', 'Operator' => 'D'])
    ->group_end()
    ->get_where('cdr', ['Dept' => 'Sale'])
    ->result();

If desired, the or_like() can be more DRY with:
->or_like(array_fill_keys(['Circle', 'CLI', 'Operator'], 'D'))

Rendered SQL:

SELECT *
FROM `cdr`
WHERE (
`Circle` LIKE '%D%' ESCAPE '!'
OR  `CLI` LIKE '%D%' ESCAPE '!'
OR  `Operator` LIKE '%D%' ESCAPE '!'
)
AND `Dept` = 'Sale'

Upvotes: 0

AdrienXL
AdrienXL

Reputation: 3008

You should read the codeigniter doc, it's an easy one :

$this->db->where("(Circle LIKE '%D%' OR CLI LIKE '%D%' OR Operator LIKE '%D%')")
         ->where("dept", "Sale");
$query = $this->db->get("cdr");

http://www.codeigniter.com/user_guide/database/active_record.html

Upvotes: 1

Related Questions