CODEWITHSUNDEEP
phpmysqlcakephpcakephp-3.0query-builder
Imri Persiado
Imri Persiado

Reputation: 1889

Counting row occurrences and printing them

A MySQL table named Tags contains 2 columns: tags and video_id with over 100k rows. None of them is primary key which means that values in the tags column might occur few times with different video_id, for example:

column names:  |tags|video_id|
values:         tag1 video1
                tag1 video2
                tag2 video4
                tag2 video5
                tag2 video6

How can I count all the occurrences of each tag and print it? Something like:

tag1 (2 Videos)

tag2 (3 Videos)

My website is based on CakePHP-3, the way I get the rows from the table is like that:

$query = $this->Tags->find('all');
$tags = $this->paginate($query);

The variable tags now has the results and that's how I print them:

foreach ($tags as $tag):
        echo $tag->tags."<br>";
endforeach;

Upvotes: 1

Views: 77

Answers (2)

tarikul05
tarikul05

Reputation: 1913

You can use cakephp count() function and Group options

$query = $this->Tags->find('all');
$tags = $query->select([ 
              'tags',
              'count' => $query->func()->count('*')
            ])
     ->group('tags');

you can get result by 

foreach ($tags as $tag):
        echo $tag->tags."(".$tag->count." Videos)";
endforeach;

Upvotes: 2

ndm
ndm

Reputation: 60493

You can do so by leveraging basic SQL logic, that is for example count video_id (or *, which might give you better performance under certain circumstances) and group by tags.

$query = $this->Tags->find();
$query = $query
    ->select([
        'tags',
        'count' => $query->func()->count('video_id')
    ])
    ->group('tags');

$tags = $this->paginate($query);

This will create a query similar to:

SELECT tags, COUNT(video_id) as count
FROM tags
GROUP BY tags

The resulting entities will hold the count property that you can access like any other property. Here's a small example, additionally using plural aware translation functions (surely not required, but might be useful):

foreach ($tags as $tag):
    echo $tag->tags . ' (' .
        __n('{0} Video', '{0} Videos', $tag->count, $tag->count) .
    ')<br>';
endforeach;

See also

Upvotes: 3

Related Questions