Codeigniter mysql连接同一个表两次 [英] Codeigniter mysql join same table twice

问题描述

解决方案

我认为codeigniter调用错过了在连接函数中做AS的可能性，或者至少是我知道到目前为止，任何更正将不胜感激。因此我这样做而不是原始调用

  $ sql =
 SELECT default_mailsystem。*，
 recipent.first_name AS modtager，
 sender.first_name AS afsender 
 
从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（）; 
 





---

问题 p>

我想使用PyroCms在Codeigniter中创建一个邮件系统。
在我的邮件表中，我有一个recipent行和一个sender行，其中包含发件人和recipent的用户ID。要从ID中查找用户名尝试加入表toghetter，但它只是返回我这个错误：

错误号码：1066

 不唯一的表/别名：'default_users'
 
 SELECT`default_mailsystem`。*，`default_users`.`username` AS modtager， default_users``作为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 
 
文件名：/hsphere/local/home/brightmedia/reuseable.dk/modules/mail/models/mail_m.php 
 
行号：13 
  

我的代码如下：

  $ 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（）; 
  

有趣的是，如果我删除最后的join操作，邮件的收件人都工作得很好。

任何建议将不胜感激。
Sincerly
Jonas

解决方案

这很简单

  $ 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 '，'DESC'）; 
 return $ this-> db-> get（'mailsystem'） - > result（）; 
  

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

解决方案

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

这篇关于Codeigniter mysql连接同一个表两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！