php - MySQL 在 CodeIgniter 中两次连接同一个表中的两个不同列

Question

我有 announcements 表，其中存储 announcements.catid (类别 ID)。

类别表categories用于通过使用categories.pid进行嵌套。

我想做的，是将类别一一嵌套在多维数组中。

这是我希望它如何位于突出显示部分内的图像:

这是代码:

public function get_mixed( $limit = NULL, $offset = NULL )
{
    $this->db
        ->select('
            announcements.id,
            announcements.catid,
            announcements.uid,
            categories.title AS section,
            categories.title AS category,
            announcements.title,
            announcements.text,
            announcements.views,
            announcements.created,
            announcements.modified,
            announcements.pubdate
        ')
        ->join('categories', 'announcements.catid = categories.id', 'left')
        ->join('categories as section', 'categories.pid = section.id', 'left')
        ->order_by('created DESC, '.$this->_order_by);

    return $this->db->get( $this->_table_name, $limit, $offset )->result();
}

我首先尝试创建一个 section 别名，但它得到了相同的结果。

也许有更好的方法？

Answer 1

如果您将其中一个categories 连接别名为section，那么您需要从该引用您想要的任何字段categories 表为 section.[field](例如 section.title)

不知道您到底想要什么，我对您修改后的查询的最佳猜测是:

$this->db
    ->select('
        announcements.id,
        announcements.catid,
        announcements.uid,
        section.title AS section,
        categories.title AS category,
        announcements.title,
        announcements.text,
        announcements.views,
        announcements.created,
        announcements.modified,
        announcements.pubdate
    ')
    ->join('categories', 'announcements.catid = categories.id', 'left')
    ->join('categories as section', 'categories.pid = section.id', 'left')
    ->order_by('created DESC, '.$this->_order_by);