CODEWITHSUNDEEP
phpsqlcodeigniteractiverecord
Jack
Jack

Reputation: 327

Codeigniter - Active record - sql - complex join

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?

Upvotes: 6

Views: 6614

Answers (2)

zokibtmkd
zokibtmkd

Reputation: 2183

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

Upvotes: 2

gen_Eric
gen_Eric

Reputation: 227200

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

Upvotes: 7

Related Questions