SELECT query to COUNT qualifying rows from an indirectly related table with CodeIgniter's active record syntax

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
– Jakub, Commented Mar 7, 2011 at 5:52

gen_Eric · Accepted Answer · 2011-03-04 15:56:11Z

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 4, 2011 at 15:56

answered Mar 4, 2011 at 15:26

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zokibtmkd · Accepted Answer · 2011-03-07 05:40:27Z

2

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

answered Mar 7, 2011 at 5:40

zokibtmkd

2,1831 gold badge22 silver badges24 bronze badges

1 Comment

mickmackusa Apr 19 at 2:30

This general advice makes no attempt to resolve the asked question.

mickmackusa · Accepted Answer · 2025-04-19 03:43:34Z

For full observability of the published works count per tag, use LEFT JOINs to relate the tables, GROUP BY the tag columns, and use SUM() on the condition which checks the draft column (COALESCE to zero if you don't want NULL values in the result set). This approach will show all tags; even if their count is 0. SQLize Demo

public function getPublishedWorksPerTag(): array
{
    return $this->db
        ->select('t.id, t.name, COALESCE(SUM(w.draft = 0), 0 published_count')
        ->from('tags t')
        ->join('tags_to_work ttw', 't.id = ttw.tag_id', 'left')
        ->join('work w', 'ttw.work_id = w.id', 'left')
        ->group_by(['t.id', 't.name'])
        ->order_by('published_count', 'DESC')
        ->get()
        ->result();
}

Collectives™ on Stack Overflow

SELECT query to COUNT qualifying rows from an indirectly related table with CodeIgniter's active record syntax

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related