如何在Codeigniter中两次联接同一张表并分配表别名? [英] How to join the same table twice and assign table aliases in Codeigniter?

问题描述

我正在尝试使用PyroCms在Codeigniter中创建一个邮件系统. 在我的邮件表中，我有一个收件人"行和一个发件人"行，其中包含发件人和收件人的用户ID.为了从ID中检索用户名，我正在尝试将表联接在一起，但是它只是向我返回此错误:

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:

错误号:1066

Error Number: 1066

不唯一的表/别名:'default_users'

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

文件名:/hsphere/local/home/brightmedia/reuseable.dk/modules/mail/models/mail_m.php

Filename: /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();

有趣的是，如果我删除了最后一个加入"操作，而只将其加入邮件的收件人，则一切正常.

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.

解决方案

(编辑器注意:决不能在问题中发布解决方案！如果您有未作为答案代表的解决方案，请发布新的有启发性的答案并接受它.请勿通过编辑内容混淆页面结构包含答案的问题.)

我发现codeigniter调用错过了在join函数内部执行AS的可能性，或者至少到目前为止，我所知道的任何纠正都将受到赞赏.为此，我这样做是代替了原来的电话:

I figured that the codeigniter call missed out the possibility to do a AS inside of a join function, or at least that's what i know so far, any correction will be appreciated. ThereforE 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();

推荐答案

这很简单

$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中两次联接同一张表并分配表别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！