CodeIgniter active record SELECT query with JOIN and WHERE IN subquery

Question

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();
}

Nouphal.M · Accepted Answer · 2014-01-30 14:31:44Z

1

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();

answered Jan 30, 2014 at 14:31

Nouphal.M

6,3281 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kluverkamp · Accepted Answer · 2014-01-30 14:18:04Z

0

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();

answered Jan 30, 2014 at 14:18

Kluverkamp

1

1 Comment

tigz Over a year ago

Yes it works perfectly fine. But I would like to know if its possible sending the same query using $this->db->wher_not_in()

gen_Eric · Accepted Answer · 2014-01-30 14:38:56Z

0

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);

answered Jan 30, 2014 at 14:38

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

1 Comment

tigz Over a year ago

Wow, this also works. Looks like I need to learn more about arrays

mickmackusa · Accepted Answer · 2024-09-03 05:25:40Z

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();
}

Collectives™ on Stack Overflow

CodeIgniter active record SELECT query with JOIN and WHERE IN subquery

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related