Count in contain Cakephp 3

Question

I have a table Post and this has a has-many association with a table Stars.

I can get all the associated data using:

$this->Posts->find()->contain(['Stars']);

That works well.

But I want to count the Stars. I have tried this but its not working:

$this->Posts->find->contain([
    'Stars' => function($q) {
        return $q->select(['total' => $q->func()->count('Stars.post_id')]);
    }
]);

//I've also tried this
...
...$q->select(['total' => "COUNT(Stars.post_id)"]);
...
//Also fail

This does not return the number of associated Stars.

Is there something wrong or should do it some other way?

Thanks

Answer 1

you have to select also the foreign key otherwise cake is not able to join the tables. And you have also to group the result

'Stars' => function($q) {
    $q->select([
         'Stars.post_id',
         'total' => $q->func()->count('Stars.post_id')
    ])
    ->group(['Stars.post_id']);

    return $q;
}

Answer 2

As here we have used total as virtual field, can be create more like this in same model as:

 public function index()
    {
        $checklist = TableRegistry::get('Checklists');
        $query = $checklist->find()
            ->where('Checklists.is_deleted = 0')
            ->contain([
                'ChecklistTitles' => function($q) {
                    return $q -> select([
                        'ChecklistTitles.title',
                        'ChecklistTitles.checklist_id'
                ]);

            },
              'ChecklistTypes' => function($w){
                  return $w->select(['ChecklistTypes.type']);
            },
              'AssignedChecklists' => function($e){
                            $e->select([
                                'AssignedChecklists.checklist_id',
                                'completed' => $e->func()
                    ->count('AssignedChecklists.checklist_id'),
                        ])
                        ->group(['AssignedChecklists.checklist_id'])
                        ->where(['AssignedChecklists.is_deleted = 0 AND AssignedChecklists.checklist_status = 2']);
                    return $e;
                }
                ]);
        // ->toArray();  
        // pr($query);die;   
               $this->paginate = [
            'limit' => 20,
            'sortWhitelist' => [
                'id', 'checklist_title', 'checklist_type'
            ]
        ];
        $this->set('query', $this->paginate($query));
        $this->set(compact('checklists','query'));
        $this->set('_serialize', ['checklists','query']);

    }

As here I have calculated completed, I want to calculate cancelled with different where condition, what will be the syntax for it in cakephp3?

Answer 3

Try this:

$total = $this->Posts->find()->contain(['Stars'])->count();

As refereed in the cookbook.