title: Codeigniter - Active record - sql - complex join
body: <p>I have a function that retrieves all tags from a table:</p>
  
  <pre><code>function global_popular_tags() {
      $this-&gt;db-&gt;select('tags.*, COUNT(tags.id) AS count');
      $this-&gt;db-&gt;from('tags');
      $this-&gt;db-&gt;join('tags_to_work', 'tags.id = tags_to_work.tag_id');
      $this-&gt;db-&gt;group_by('tags.id');
      $this-&gt;db-&gt;order_by('count', 'desc'); 
      $query = $this-&gt;db-&gt;get()-&gt;result_array();
      return $query;
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>