Think Cakephp 3

Question

I have a Post table, and it has a has-many relationship with the Star table.

I can get all related data using:

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

It works well.

But I want to count the Stars. I tried this but did not work:

 $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 stars associated.

Is there something wrong or should it do it differently?

thanks

Answer 1

You also need to select a foreign key, otherwise the cake will not be able to join the tables. And you can also 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 we used total as a virtual field here, you can create a more similar one in the 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 I calculated completed here, I want to calculate canceled with another, where is the condition, what will be the syntax for it in cakephp3?

Answer 3

Try the following:

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

As indicated in the cookbook .