Codeigniter - active record - sql - complex connection

Question

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". There is a “draft” column in the “work” table with values ​​of 1 or 0. I want COUNT (tags.id) to take into account whether the work with a specific tag works in draft mode (1) or not.

Say that there are 10 works marked, for example, by “design”. COUNT will be 10. But 2 of these parts of the job are in draft mode, so the COUNT value should really be 8. How do I do this?

Answer 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 the addition:

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

Answer 2

You can use pure sql instead of using the active write class, I myself have been working with CI for more than 2 years, and most of the time I avoid the active write class, because direct sql is much easier to debug and write complex queries. This is how I used it.

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