Retrieving data with count for each row in CakePHP

Question

I need to do what the title says.

I have a specific query that I have to show. I don't want to do a raw query. I want to do like any others query I made.

This is the result that I need to display in my index:

Of course is the result of my database query that I have to display. I need to represent the same in my site.

The following method is what I made. But i don't know how to do the part of count (it's the only part that I need to finish the query):

public function index()
{
    $country= $this -> Auth -> User()['country_fk'];
    $this->paginate['contain'] = ['Dogs','Dogs.Human','Dogs.Human.Cities','Users'];
    $this->paginate['conditions'] = ['Users.isHuman' => 0, 'Cities.country_fk' => $country];
    $this->paginate['order'] = array('Badges.id' => 'desc');
    $badges= $this->paginate($this->Badges);
    $this->set(compact('badges'));
    $this->set('_serialize', ['badges']);
}

Some example do the query and then make a count but I think is not what I'm looking for.

Thanks a lot.

EDIT After arilia's answer, In my controller I have the following lines:

public function index()
{
    $country= $this -> Auth -> User()['country_fk'];
    $this->Turnos->virtualFields = array('count' => 'COUNT(*)');
    $this->paginate['contain'] = ['Dogs','Dogs.Human','Dogs.Human.Cities','Users'];
    $this->paginate['conditions'] = ['Users.isHuman' => 0, 'Cities.country_fk' => $country];

     $this->paginate['group'] = array('Badges.value2');
     $this->paginate['order'] = array('Badges.value2' => 'desc');

     $badges= $this->paginate($this->Badges);
     $this->set(compact('badges'));
     $this->set('_serialize', ['badges']);
}

This is what I do for showing the values:

<thead>
        <tr>
            <th><?= $this->Paginator->sort('idBadge', 'ID') ?></th>
            <th><?= $this->Paginator->sort('name', 'Name') ?></th>
            <th><?= $this->Paginator->sort('value1', 'Value 1') ?></th>
            <th><?= $this->Paginator->sort('value2', 'Value 2') ?></th>
            <th><?= $this->Paginator->sort('count', 'Quantity') ?></th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($badges as $badge): ?>
        <tr>
            <td><?= h($badge->idBadge) ?></td>
            <td><?= h($badge->name) ?></td>
            <td><?= h($badge->value1) ?></td>
            <td><?= h($badge->value2) ?></td>
            <td><?= h($badge->count) ?></td>
        </tr>
    <?php endforeach; ?>

are you sure is cake2? For what I know cake2 does not returns objects after a paginate call... — arilia
Sorry, I've just corrected it. I think is 3.0. It's a old project and didn't remember well the version of cake. — Faustino Gagneten

arilia arilia · Accepted Answer · 2017-10-24T06:57:10

First you should create a virtual field

$this->Badges->virtualFields['count'] = 'COUNT(*)';

then you have to group

$this->paginate['group'] = array('the field you want to group by');

But you have to tell the paginator component that you want that field in the query

$this->paginate['fields'] = array('count', ...);

edit: the above code is for cake2

For cake 3

simply add the field and the group to the paginator array

$this->paginate['fields'] = 
[
    'count' => 'COUNT(*)'
    // other fields you want to retrieve go here
];

$this->paginate['group'] = ['the field you want to group by'];

Anyway I would use the query builder this way

 $badges = $this->Badges->find()
     ->select(['count' => 'COUNT(*)'])
     ->select($this->Badges) // this command tells the query builder to    
                             // select all the fields of badges table
                             // other than count
     ->select($this->Badges->Dogs) 
     ->select($this->Badges->Dogs->Humans) 
     ->select(... and so on for every model you want ...)
     ->contain(['Dogs','Dogs.Human','Dogs.Human.Cities','Users'])
     ->where(['Users.isHuman' => 0, 'Cities.country_fk' => $country])
     ->group(['the field to group'])
     ->order(['Badges.id' => 'desc']);

$badges= $this->paginate(badges);

Retrieving data with count for each row in CakePHP

1 Answers