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

我正在尝试用PyroCms在Codeigniter中制作一个邮件系统。在我的邮件表中，我有一个"；接收器"；行和一个"行"；发送者"；行，其中包含发件人和收件人的用户id。为了从id中检索用户名，我试图将表连接在一起，但它只会返回以下错误：

错误编号：1066

不是唯一的表/别名："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

文件名：/hphere/local/home/brightmedia/reusable.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();

有趣的是，如果我去掉最后一个"；加入"；操作，让它只加入邮件的收件人，一切都很好。

这是一个非常简单的

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

您是否尝试在联接函数中强制使用别名（"AS"运算符在select子句中不起作用…）？

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

使用这样的别名-

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

您可以使用这个：

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

*如果您使用任何数据库前缀，请使用此*

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

Its very easy to get data from single table
$this->db->select('a.*,b.fname AS cname,c.fname as uname'); 
$this->db->from('tbl_menus a'); 
$this->db->join('tbl_admin_login b', 'b.id = a.create_by', 'left'); 
$this->db->join('tbl_admin_login c', 'c.id = a.update_by', 'left'); 
$this->db->order_by('a.id', 'desc'); 
return $this->db->get()->result_array();

在这个线程的时候，我发现codeigniter调用错过了在联接内部执行AS的可能性，因此解决了问题：

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

您可以尝试核心PHP

SELECT `custome_module`.`id`, `custome_module`.`name`, min(l1.nmark_completed) as call_waiter, min(l2.nmark_completed) as bill, min(l3.nmark_completed) as tray, min(l4.nmark_completed) as ordera
FROM `custome_module`
LEFT JOIN `restaurant_logs` as `l1` ON `custome_module`.`id` = `l1`.`nmodule_id` AND `l1`.`ntype` = 1
LEFT JOIN `restaurant_logs` as `l2` ON `custome_module`.`id` = `l2`.`nmodule_id` AND `l2`.`ntype` = 2
LEFT JOIN `restaurant_logs` as `l3` ON `custome_module`.`id` = `l3`.`nmodule_id` AND `l3`.`ntype` = 6
LEFT JOIN `restaurant_logs` as `l4` ON `custome_module`.`id` = `l4`.`nmodule_id` AND `l4`.`ntype` = 5
WHERE `custome_module`.`nbranch_id` = '142'
GROUP BY `custome_module`.`id`

以及在码点火器中

$this->db->select('custome_module.id, custome_module.name, min(l1.nmark_completed) as call_waiter, min(l2.nmark_completed) as bill,min(l3.nmark_completed) as tray,min(l4.nmark_completed) as ordera');
$this->db->from("custome_module");
$this->db->join('restaurant_logs as l1', 'custome_module.id = l1.nmodule_id AND l1.ntype = 1', 'left');
$this->db->join('restaurant_logs as l2', 'custome_module.id = l2.nmodule_id AND l2.ntype = 2', 'left');
$this->db->join('restaurant_logs as l3', 'custome_module.id = l3.nmodule_id AND l3.ntype = 6', 'left');
$this->db->join('restaurant_logs as l4', 'custome_module.id = l4.nmodule_id AND l4.ntype = 5', 'left');
$this->db->where('custome_module.nbranch_id', $this->data['user_session']['nid']);
$this->db->get();