How to separate logic in a Codeigniter active record WHERE clause [duplicate]

Question

In CodeIgniter, I have a query built from this active record script:

$this->db->select('*', false);
$this->db->from('video as v');
$this->db->where('v.start_date <=', 'NOW()', false);
$this->db->like('v.title', $keywords);
$this->db->or_like('v.title_tw', $keywords);
$this->db->or_like('v.title_cn', $keywords);
$query = $this->db->get();
return $query->result_array();

My intended WHERE logic:

start_date <= NOW
AND
(
    title like $keywords
    OR title_tw like $keywords
    OR title_cn like $keywords
)

However, it renders as:

start_date <= NOW OR title like $keywords OR title_tw like $keywords OR title_cn like $keywords

How to fix that?

---

Update: here is my full method:

public function search_video($keywords)
{
    $this->db->select('v.*,t.name,t.name_tw,t.name_cn, t.image_url as profile_pic, COALESCE(b.duration, bt.duration, bc.duration) AS duration, COALESCE(b.thumbnail, bt.thumbnail, bc.thumbnail) as thumbnail', false);
    $this->db->from('video as v');
    $this->db->join('teacher as t', 'v.teacher_id = t.id');
    $this->db->join('video_tag as vt', 'v.id = vt.video_id', 'left');
    $this->db->join('brightcove as b', 'v.video = b.video_id', 'left');
    $this->db->join('brightcove as bt', 'v.video_tw = bt.video_id', 'left');
    $this->db->join('brightcove as bc', 'v.video_cn = bc.video_id', 'left');
    $this->db->where('v.is_delete', false);
    $this->db->where('v.start_date <=', 'NOW()', false);
    $this->db->like('v.title', $keywords);
    $this->db->or_like('v.title_tw', $keywords);
    $this->db->or_like('v.title_cn', $keywords);
    $this->db->or_like('vt.tag', $keywords);
    $this->db->group_by('v.id');
    $query = $this->db->get();
    return $query->result_array();
}

Answer 1

For CodeIgniter 3.x:

You can use Query grouping to use parentheses in query:

    $this->db->select('v.*,t.name,t.name_tw,t.name_cn, t.image_url as profile_pic, COALESCE(b.duration, bt.duration, bc.duration) AS duration, COALESCE(b.thumbnail, bt.thumbnail, bc.thumbnail) as thumbnail', false);
        $this->db->from('video as v');
        $this->db->join('teacher as t', 'v.teacher_id = t.id');
        $this->db->join('video_tag as vt', 'v.id = vt.video_id', 'left');
        $this->db->join('brightcove as b', 'v.video = b.video_id', 'left');
        $this->db->join('brightcove as bt', 'v.video_tw = bt.video_id', 'left');
        $this->db->join('brightcove as bc', 'v.video_cn = bc.video_id', 'left');
        $this->db->where('v.is_delete', false);
        $this->db->where('v.start_date <=', 'NOW()', false);
        $this->db->group_start();
        $this->db->like('v.title', $keywords);
        $this->db->or_like('v.title_tw', $keywords);
        $this->db->or_like('v.title_cn', $keywords);
        $this->db->or_like('vt.tag', $keywords);
        $this->db->group_end();
        $this->db->group_by('v.id');
        $query = $this->db->get();

For CodeIgniter 2.x:

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

$this->db->select('v.*,t.name,t.name_tw,t.name_cn, t.image_url as profile_pic, COALESCE(b.duration, bt.duration, bc.duration) AS duration, COALESCE(b.thumbnail, bt.thumbnail, bc.thumbnail) as thumbnail', false);
    $this->db->from('video as v');
    $this->db->join('teacher as t', 'v.teacher_id = t.id');
    $this->db->join('video_tag as vt', 'v.id = vt.video_id', 'left');
    $this->db->join('brightcove as b', 'v.video = b.video_id', 'left');
    $this->db->join('brightcove as bt', 'v.video_tw = bt.video_id', 'left');
    $this->db->join('brightcove as bc', 'v.video_cn = bc.video_id', 'left');
    $this->db->where('v.is_delete', false);
    $this->db->where('v.start_date <=', 'NOW()', false);
    $this->db->where("( v.title LIKE '%{$keywords}%'", null, FALSE);
    $this->db->or_like('v.title_tw', $keywords);
    $this->db->or_like('v.title_cn', $keywords);
    $this->db->where("OR vt.tag LIKE '%{$keywords}%' )", null, FALSE);
    $this->db->group_by('v.id');

Answer 2

You could try it like this:

$sql = SELECT * FROM video as v where v.start_date <= NOW() AND (v.title like ? OR v.title_tw like ? or v.title_cn like ?);
$query = $this->db->query($sql, array($keywords, $keywords, $keywords));