Codeigniter mysql连接两次相同的表

Question

Solution

I figured that the codeigniter call missed out the possibility to do a AS inside of a join function, or atleast thats what i know so far, any correction will be appreciated. Therefor i did this instead of the original call

        $sql = "
            SELECT default_mailsystem.*,
                recipent.first_name AS modtager, 
                sender.first_name AS afsender

            FROM default_mailsystem

            LEFT JOIN default_profiles AS recipent 
                ON recipent.id = default_mailsystem.id

            LEFT JOIN default_profiles AS sender 
                ON sender.id = default_mailsystem.id
    ";
    return $this->db->query($sql)->result();

---

Question

Im trying to make a mailsystem 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 recipent. To retrive usernames from the ids im trying to join the table toghetter, 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 recipent of the mail it all works out well.

Any suggestions will be appreciated. Sincerly Jonas

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