Codeigniter - 活动记录 - sql - 复杂连接

I have a function that retrieves all tags from a table:

我有一个从表中检索所有标签的函数：

function global_popular_tags() {
    $this->db->select('tags.*, COUNT(tags.id) AS count');
    $this->db->from('tags');
    $this->db->join('tags_to_work', 'tags.id = tags_to_work.tag_id');
    $this->db->group_by('tags.id');
    $this->db->order_by('count', 'desc'); 
    $query = $this->db->get()->result_array();
    return $query;
}

I have another table called 'work'. The 'work' table has a 'draft' column with values of either 1 or 0. I want the COUNT(tags.id) to take into account whether the work with the specific tag is in draft mode (1) or not.

我有另一张叫做“工作”的桌子。 'work'表有一个'draft'列，其值为1或0.我希望COUNT（tags.id）考虑具有特定标记的工作是否处于草稿模式（1）。

Say there are 10 pieces of work tagged with, for example, 'design'. The COUNT will be 10. But 2 of these pieces of work are in draft mode, so the COUNT should really be 8. How do I manage this?

假设有10件作品被标记为“设计”。 COUNT将是10.但是这些工作中有2个处于草稿模式，因此COUNT应该是8.我该如何管理？

2 个解决方案

#1

Try changing:

尝试改变：

$this->db->from('tags');
$this->db->join('tags_to_work', 'tags.id = tags_to_work.tag_id');

To:

至：

$this->db->from('tags, work');
$this->db->join('tags_to_work', 'tags.id=tags_to_work.tag_id AND work.id=tags_to_work.work_id');

And Adding:

并添加：

$this->db->where('work.drafts', 0);

#2

You can use pure sql instead of using the active record class, I myself are working with CI for over 2 years and most of the time I am avoiding the active record class, cause straight sql is much easier to debug and write complex queries. This is how i would use it.

您可以使用纯sql而不是使用活动记录类，我自己正在使用CI超过2年，并且大部分时间我都在避免使用活动记录类，因为直接sql更容易调试和编写复杂查询。这就是我如何使用它。

$sql = "SELECT...your sql here";
$q = $this->db->query($sql);
...
//Do something with your query here

#1