Active Record Not Unique Table Issue

Question

I keep getting

Not unique table/alias: 'users'

When running my Active Record query...

$this->db->select('*');
$this->db->from('faults');
$this->db->join('users','users.user_id = faults.customer_id');
$this->db->join('users','users.user_id = faults.engineer_id', 'left outer');
$this->db->where('faults.fault_id', $fault_id);

Which translates as (In MySQL) :

SELECT * FROM (`faults`) JOIN `users` ON `users`.`user_id` = `faults`.`customer_id` LEFT OUTER JOIN `users` ON `users`.`user_id` = `faults`.`engineer_id` WHERE `faults`.`fault_id` = '1'

I have had a look at this answer but have no idea how I would convert this to my active record query?

Any help appreciated.

Cheers

look at aliasing one of the joins - you can't join the same table with the same name twice. — sevenseacat, Feb 24 '14 at 15:07
http://stackoverflow.com/questions/9158390/codeigniter-mysql-join-same-table-twice — Mattt, Feb 24 '14 at 15:07
You are using `users` twice for the joins, you need add an alias for one of the joins — superphonic, Feb 24 '14 at 15:08

score 1 · Accepted Answer · answered Feb 24 '14 at 15:13

You need to add alias'...

$this->db->select('*');
$this->db->from('faults AS F0');
$this->db->join('users' AS U0, 'U0.user_id = F0.customer_id');
$this->db->join('users' AS U1, 'U1.user_id = F0.engineer_id', 'left outer');
$this->db->where('F0.fault_id', $fault_id);

score 0 · Answer 2 · edited Feb 24 '14 at 15:11

0

private $_fautls_table = 'fautls';

$this->db->from('fautls as f')
$this->db->join($this->_users_table . ' AS u', 'u.user_id = f.customer_id'); 
$this->db->join($this->_users_table . ' AS uu', 'uu.users_id = f.engineering_id');

edited Feb 24 '14 at 15:11

M Khalid Junaid

63,861
10
90
118

answered Feb 24 '14 at 15:09

Kisaragi

2,198
3
16
28

Active Record Not Unique Table Issue

2 Answers2