Attach a query with multiple ON states not working in Codeigner?
I need a request like this
SELECT * FROM (`users`)
LEFT JOIN `users_phone_numbers`
ON `users`.`id`= `users_phone_numbers`.`user_id`
LEFT JOIN `phone_number`
ON (`phone_number`.`id`= `users_phone_numbers`.`phone_num_id` AND users_phone_numbers.is_active = 1)
WHERE `users`.`id` = 56
I have some code like this in codeigniter
$this->db->select('*');
$this->db->from('users');
$this->db->join('users_phone_numbers',
'users.id= users_phone_numbers.user_id',
'left');
$this->db->join('phone_number',
'(phone_number.id= users_phone_numbers.phone_num_id AND users_phone_numbers.is_active = 1)',
'left');
$this->db->where('users.id = '. $id);
$result = $q->result_array();
But I got this error
+3
source to share
3 answers
If you check Codeigniter's handling of the condition function, you see the following:
// Strip apart the condition and protect the identifiers
if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))
Codeigniter removes the opening brackets from the request. Can you not move the extra LEFT JOIN clause to the WHERE clause to get around this?
EDIT:
$this->db->select('*');
$this->db->from('users');
$this->db->join('users_phone_numbers',
'users.id= users_phone_numbers.user_id',
'left');
$this->db->join('phone_number',
'phone_number.id= users_phone_numbers.phone_num_id)',
'left');
$this->db->where(array('users.id => '. $id, 'users_phone_numbers.is_active' => 1));
$result = $q->result_array();
+3
source to share