CODEWITHSUNDEEP
phpcodeigniterjoinsubqueryquery-builder
tigz
tigz

Reputation: 3

CodeIgniter active record SELECT query with JOIN and WHERE IN subquery

I'm trying to send this query:

SELECT * FROM employees
INNER JOIN authorization ON authorization.Employee_ID=employees.Employee_ID
WHERE authorization.role='Team Leader'
AND authorization.Employee_ID NOT IN(SELECT Employee_ID FROM team_leaders)

This query is used to select employees who have a Team Leader position(role) and not assigned to a team.

I'm having trouble doing this query in CodeIgniter's active records

My incorrect model code:

function teamleaders()
{
    $this->db->select('Employee_ID');
    $this->db->from('team_leaders');
    $query=$this->db->get();
    $teamleaderid=$query->result_array();
        //get teamleaders id who are assigned in a team
    
    $this->db->select('employees.Employee_ID,employees.First_Name,employees.Last_Name');
    $this->db->from('employees');
    $this->db->join('authorization','authorization.Employee_ID=employees.Employee_ID');
    $this->db->where('authorization.role','Team Leader');
    $this->db->where_not_in('authorization.Employee_ID',$teamleadersid);
    $query=$this->db->get();

    return $query->result_array();
}

Upvotes: 0

Views: 77

Answers (4)

mickmackusa
mickmackusa

Reputation: 47883

The subquery can be avoided by leveraging a LEFT JOIN and checking for a NULL row in the team_leaders table.

You are wise to avoid passing an array to where_in() or where_not_in(); when the array is empty, CodeIgniter will create an invalid/empty IN() condition.

The below should successfully return employees which have a role of Team Leader, but are not found in the team_leaders table.

public function teamleaders(): array
{
    return $this->db
        ->select('e.*')
        ->join('authorization a', 'e.Employee_ID = a.Employee_ID')
        ->join('team_leaders tl', 'e.Employee_ID = tl.Employee_ID', 'LEFT')
        ->where('a.role', 'Team Leader')
        ->where('tl.Employee_ID IS NULL')
        ->get('employees e')
        ->result_array();
}

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227220

Your $teamleaderid array is not in the format you think it's in. It's an array of arrays, not an array of IDs.

Try this:

$teamleaderid = array_map(function($a){
    return $a['Employee_ID'];
}, $teamleaderid);

Upvotes: 0

Nouphal.M
Nouphal.M

Reputation: 6344

Yes you can do it Try,

   $this->db->select('employees.*');
   $this->db->from('employees');
   $this->db->join('authorization','authorization.Employee_ID=employees.Employee_ID');
   $this->db->where('authorization.role','Team Leader');
   $this->db->where('authorization.Employee_ID NOT IN (SELECT Employee_ID FROM team_leaders)');
   $query=$this->db->get();

Upvotes: 1

Kluverkamp
Kluverkamp

Reputation: 1

What about this:

$sql="SELECT * FROM employees
INNER JOIN authorization ON authorization.Employee_ID=employees.Employee_ID
WHERE authorization.role='Team Leader'
AND authorization.Employee_ID NOT IN(SELECT Employee_ID FROM team_leaders)";

return $this->db->query($sql)->result_array();

Upvotes: 0

Related Questions