Codeigniter - Active record - sql - complex join
- by Jack
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.
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?
