How do you use COUNT (*) with find ('list') in CakePHP 3?

Question

In CakePHP 3, I had a model called “Articles” and a “subject” field, and I ran into a trial block trying to get a list of the 100 most commonly used article subjects.

The following SQL code selected all possible fields, not COUNT(*) :

 $articles->find('list', [ 'keyField' => 'subject', 'valueField' => 'COUNT(*)' ]) ->group('subject') ->order(['COUNT(*)' => 'DESC']) ->limit(100) ->toArray(); 

Then I remembered "CakePHPs ORM offers an abstraction for some commonly used SQL functions." . But the following code led to "Error: function name must be a string":

 $countFunc = $this->find()->func()->count('*'); $articles->find('list', [ 'keyField' => 'subject', 'valueField' => $countFunc ]) ->group('subject') ->order([$countFunc => 'DESC']) ->limit(100) ->toArray(); 

Answer 1

Fortunately, Jose passed me this trick that works like a charm:

 $articles->find('list', [ 'keyField' => 'subject', 'valueField' => 'count' ]) ->select([ 'subject', 'count' => $this->find()->func()->count('*') ]) ->group('subject') ->order(['count' => 'DESC']) ->limit(100) ->toArray(); 

Answer 2

  $query = $articles->find(); $query->select(['count' => $query->func()->count('*')]); $StaticPages = $query->toArray(); $StaticPagesCount = $StaticPages[0]->count;