How to join the same table twice and assign table aliases in Codeigniter?

Question

I'm trying to make a mail system in Codeigniter with the PyroCms. In my mail table, I have a "recipent" row and a "sender" row which contains the user id of the sender and recipient. To retrieve usernames from the ids, I'm trying to join the table together, but it simply returns me this error:

Error Number: 1066

Not unique table/alias: 'default_users'

SELECT `default_mailsystem`.*, `default_users`.`username` AS modtager, `default_users`.`username` as afsender
FROM (`default_mailsystem`)
LEFT JOIN `default_users` ON `default_mailsystem`.`recipent` = `default_modtager`.`id`
LEFT JOIN `default_users` ON `default_mailsystem`.`sender` = `default_afsender`.`id`
ORDER BY `id` DESC

Filename: /hsphere/local/home/brightmedia/reuseable.dk/modules/mail/models/mail_m.php

Line Number: 13

My code is as follows:

$this->db->select('mailsystem.*, users.username AS modtager, users.username as afsender')
    ->join('users', 'mailsystem.recipent = modtager.id', 'left')
    ->join('users', 'mailsystem.sender = afsender.id', 'left');
$this->db->order_by('id', 'DESC');
return $this->db->get('mailsystem')->result();

The funny thing is, that if I remove the last "join" operation and leave it to only join the recipient of the mail it all works out well.

Answer 1

This is very simple

$this->db->select('mailsystem.*, users.username AS modtager, users.username as afsender')
$this->db->join('users', 'mailsystem.recipent = modtager.id AND mailsystem.sender = afsender.id', 'left')
$this->db->order_by('id', 'DESC');
return $this->db->get('mailsystem')->result();

Answer 2

Have you tried forcing an alias in the join function (the "AS" operator won't work in the select clause as you have it...)?

<?php
$this->db->select('mailsystem.*, modtager.username AS modtager_name, afsender.username as afsender_name')
    ->join('`users` `modtager`', 'mailsystem.recipent = modtager.id', 'left')
    ->join('`users` `afsender`', 'mailsystem.sender = afsender.id', 'left');

$this->db->order_by('mailsystem.id', 'DESC');

return $this->db->get('mailsystem')->result();

Answer 3

Use alias like this -

$this->db->select('mailsystem.*, users_table_a.username AS modtager, users_table_b.username as afsender')
$this->db->join('users users_table_a', 'mailsystem.recipent = users_table_a.id', 'left');
$this->db->join('users users_table_b', 'mailsystem.sender = users_table_b.id', 'left');
$this->db->order_by('id', 'DESC');
return $this->db->get('mailsystem')->result();