Combine where - like - where_in

Question

Let's say I have a model in CI returning what user(s) need to ..

$find = 'something';
$id_user = Array ( [0] => 1 [1] => 5 [2] => 20 [3] => 21 ) ;

So, I have to put that in here but something goes wrong ..

    public function find_pr_by_user ($find,$id_users) {
        $this -> db -> where_in ('req_by_id'.$id_users || 'check_by_id',$id_users || 'approve_by_id',$id_users);
        $this -> db -> where ("(ref_no LIKE '%$find%' || pr_title LIKE '%$find%' || req_date LIKE '%$find%')");
        $query = $this -> db -> get ('pr_data') ;
        return $query -> result () ;
    }

I get error Array to string conversion; input I have is from $find and $id_users that can be anything. I expected this logic, give me all array from table PR_DATA which is have %$find% on column ref_no or pr_title or req_date but only have $id_users *(1 or 5 or 20 or 21) in column req_by_id or check_by_id or approve_by_id.

Can anyone help?

Answer 1

This answer is under assumption that you need parenthesis in the first where_in() in your code.

Unfortunately, CodeIgniter does not fully support parenthesis with Active Record. Therefore you will have to use where() with more complex SQL syntax in it.

public function find_pr_by_user ($find,$id_users) {
    $id_users_glued = implode(",",$id_users);
    $this->db->where('(req_by_id IN (' . $id_users_glued . ') || check_by_id IN (' . $id_users_glued . ') || approve_by_id IN (' . $id_users_glued . '))');
    $this->db->where("(ref_no LIKE '%$find%' || pr_title LIKE '%$find%' || req_date LIKE '%$find%')");
    $query = $this->db->get('pr_data') ;
    return $query->result () ;
}

In CI's Active Record, this is how the syntax will be treated:

The FIRST WHERE will be treated as: WHERE (req_by_id IN ($id_users_glued) || check_by_id IN ($id_users_glued) || approve_by_id IN ($id_users_glued)

The $id_users_glued will produce something like 1,2,3,4,5

The SECOND WHERE will be treated as: AND (ref_no LIKE '%$find%' || pr_title LIKE '%$find%' || req_date LIKE '%$find%')

Note: I haven't tested the code because I don't have your db structure. Let me know if it's not working.