codeigniter active record left join

29
votes

I have 3 mysql tables. 

Table 1 user  
id | name  

Table 2 emails  
id | email  

Table 3 user_email  
user_id | email_id

I have no experience in query multi tables.
Using codeigniter active record, i want to find out the user email address based on the user id, pls advise if the below code is correct ? 

$CI->db->select('email');
$CI->db->from('emails');
$CI->db->where('id', $userid);
$CI->db->join('user_email', 'user_email.user_id = emails.id', 'left');
$query = $CI->db->get();
2
phpcodeigniteractiverecordleft-join

2 Answers

62
votes

You have wrong where clause you need to compare user_id from your table ,you are comparing the id of email to the provided $user_id

$CI->db->select('email');
$CI->db->from('emails');
$CI->db->where('user_id', $userid);
$CI->db->join('user_email', 'user_email.user_id = emails.id', 'left');
$query = $CI->db->get();

A more useful way is to give aliases to your tables so the tables with same columns will not have any confusion

$CI->db->select('e.email');
$CI->db->from('emails e');
$CI->db->join('user_email ue', 'ue.user_id = e.id', 'left');
$CI->db->where('ue.user_id', $userid);
$query = $CI->db->get();
0
votes

@m khalid 's answer is correct but I have created a dynamic function to achieve join with multiple tables. Check this out.

function join_records($table, $joins, $where = false, $select = '*', $orderBy = false, $direction = 'DESC'){
  $CI->select($select);
  $CI->from($table);
  foreach($joins as $join){
    $CI->join($join[0], $join[1], $join[2]);
  }
  if($where) $CI->where($where);
  if($orderBy) $CI->order_by($orderBy, $direction);
  $query = $CI->get();
  return $query->result_array();
}


Applying your question to this. 

$table = 'emails';

$joins[0][0] = 'user_email';
$joins[0][1] = 'user_email.user_id = emails.id';
$joins[0][2] = 'left';

$where['user_id'] = $userid;

You may add more join like $join1[0].. If you need to select some specific column you can define in following manner $select = 'table1.column1, table1.column2, table2.column1, table2.column2' Or if you want all the columns put a *

$this->join_records($table, $joins, $where, $select);

You may also find it here.