CodeIgniter query with AND and OR conditions in WHERE clause

Question

I'm trying to make a query with severel where clauses, but it gives wrong results. I need to see these users which have role_id=1 (that are students), which are not deactivated. I take $this->uri->segment(3) =teacher_id. And the third where clause is that I need only students that studies in the same school as the school of the teacher , which I take with $this->uri->segment(3). My query is:

public function select_students() {
    $this->db->select('*');
    $this->db->from('users');
    $this->db->where('deactivated_at = "0000-00-00 00:00:00" || deactivated_at IS NULL 
        AND role_id =1 AND school_id = ("SELECT school_id FROM users WHERE user_id=$this->uri->segment(3)")');
    $result = $this->db->get();
    return $result->result();
}

I found solution:

public function select_students()
{
    $this->db->select('*');
    $this->db->from('users');
    $this->db->where('(users.deactivated_at = "0000-00-00 00:00:00" OR users.deactivated_at IS NULL) AND users.role_id = 1 ');
    $this->db->where('users.school_id', $this->uri->segment(4));
    $result = $this->db->get();
    return $result->result();
 }

Answer 1

better use custom SQL and after that wrap with CI method. This will provide you flexibility to write any custom query as well as protect query with CI security.

public function select_students() {
   $sql = SELECT * FROM users WHERE deactivated_at = "0000-00-00 00:00:00" OR deactivated_at IS NULL AND role_id =1 AND school_id = (SELECT school_id FROM users WHERE user_id= (int) $this->uri->segment(3));
   $query = $this->db->query($sql);
   return $query->result();
 }