phpcodeigniterselectactiverecordleft-join

CodeIgniter SELECT query with LEFT JOINs and WHERE conditions is not returning any results


I'm trying to make a query, but it doesn't show me any results. My tables are:

TABLE teacher_student_conn:
    id, teacher_id, student_id, created_at
TABLE users:
    user_id, username, password, school_id, class, division, role_id, deactivated_at
TABLE schools:
    school_id, school_name

I want to fetch only students from the same school of the teacher, but not being taught by that teacher.

I take the teacher from $this->uri->segment(3) and school from $this->uri->segment(4).

I don't know exactly how to adjust my query.

My active record attempt:

public function select_students()
{
    $this->db->select('users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division');
    $this->db->from('users');       

    $this->db->join('teacher_student_conn', 'teacher_student_conn.student_id=users.user_id', 'left');

    $this->db->join('teacher_student_conn AS T', 'T.teacher_id=users.user_id', 'left');

    $this->db->join('class_divisions', 'class_divisions.id=users.division', 'left');

    $this->db->join('schools', 'schools.school_id=users.school_id', 'left');

    $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) );
    $this->db->where('teacher_student_conn.teacher_id', $this->uri->segment(3) );
    $this->db->where("teacher_student_conn.student_id IS NULL");

    $result = $this->db->get();
    return $result->result();
}

After echo $this->db->last_query(); it shows me:

SELECT users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division
FROM users
LEFT JOIN teacher_student_conn ON teacher_student_conn.student_id=users.user_id
LEFT JOIN teacher_student_conn as T ON T.teacher_id=users.user_id
LEFT JOIN class_divisions ON class_divisions.id=users.division
LEFT JOIN schools ON schools.school_id=users.school_id
WHERE (users.deactivated_at = '0000-00-00 00:00:00' OR users.deactivated_at IS NULL)
  AND users.role_id = 1
  AND users.school_id = 2
  AND teacher_student_conn.teacher_id = 10
  AND 

Then I tried a raw query and appended a where() method call:

public function select_students()
{
    $query = ("SELECT users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division FROM users
        LEFT JOIN teacher_student_conn ON teacher_student_conn.student_id=users.user_id
        LEFT JOIN teacher_student_conn as T ON T.teacher_id=users.user_id
        LEFT JOIN class_divisions ON class_divisions.id=users.division
        LEFT JOIN schools ON schools.school_id=users.school_id
        WHERE (users.deactivated_at = '0000-00-00 00:00:00' OR users.deactivated_at IS NULL) AND users.role_id = 1 AND
        users.school_id = 2 ");

    $this->db->where('teacher_student_conn.student_id', NULL);
    $result = $this->db->query($query);
    echo $this->db->last_query();
    return $result->result();   
}

But this second attempt doesn't include the condition from the where() method call.


Solution

  • This is just sql query format print your query and paste in PHPmyadmin SQL or echo $this->db->last_query();.

    public function select_students() {
    
             $query = "SELECT users.user_id, users.username, users.school_id, users.class, users.division, users.role_id, schools.school_name, schools.region,class_divisions.division FROM users"
                     . "LEFT JOIN teacher_student_conn ON teacher_student_conn.student_id=users.user_id"
                     . "LEFT JOIN teacher_student_conn as T ON T.teacher_id=users.user_id"
                     . "LEFT JOIN class_divisions ON class_divisions.id=users.division"
                     . "LEFT JOIN schools ON schools.school_id=users.school_id"
                     . "WHERE (users.deactivated_at = '0000-00-00 00:00:00' OR users.deactivated_at IS NULL) AND users.role_id = 1 AND "
                     . "users.school_id = ".$this->uri->segment(4)." AND teacher_student_conn.teacher_id = ".$this->uri->segment(4)." AND teacher_student_conn.student_id IS NULL";
            $result = $this->db->query($query);
    
            return $result->result();             
    }