Codeigniter - Active record - sql - complex join

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'. 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?

As a precaution, you need to get away from using `*` selectors in your queries, this is not good practice, always define the column names you want returned, *NEVER* do `SELECT * FROM...` — Jakub, Mar 07 '11 at 05:52

gen_Eric · Accepted Answer · 2011-03-04T15:56:11.067

7

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);

edited Mar 04 '11 at 15:56

answered Mar 04 '11 at 15:26

gen_Eric

223,194
41
299
337

Thanks, but it's not the work table that has a 'tag_id' column, it's the tags_to_work table that does. I tried: $this->db->join('tags_to_work AS ttw', 'work.id = ttw.work_id AND work.draft = 0'); but I am getting counts of 33 instead of 1, and 66 instead of 2! – Jack Mar 04 '11 at 15:50

score 2 · Answer 2 · answered Mar 07 '11 at 05:40

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 = "SELECT...your sql here";
$q = $this->db->query($sql);
...
//Do something with your query here

Codeigniter - Active record - sql - complex join

2 Answers2

Linked