0

Recently I get stuck with seemingly simple task as to output the related count number of referenced items in an query builder statement.

Here the simplified code: 

$data = TableRegistry::getTableLocator()->get('tableA')->find() ->select( [ 'tableA.term', 'tableA.termkey', 'count(tableA.termkey)' //my first though was this...but it does not work 'count' => TableRegistry::getTableLocator()->get('tableA')->find()->func()->count('*') //ok ], ) ->join([ .... ] ]) ->where( .... )->group( .... )->order( .... );

Right now, after some documentation lookup I had finally find an solution with rather exotic syntax.

TableRegistry::getTableLocator()->get('tableA')->find()->func()->count('*') //ok, works so far..but do I need this all stuff for an simple count?

It is really the desired approach to use a count function within an query builder?

Are there any better solution than this?

Improve this question

2 Answers 2

Reset to default
3

You could eliminate a bit of redundancy by initializing the query first:

$query = TableRegistry::getTableLocator()->get('tableA')->find(); $query = $query->select([ 'tableA.term', 'tableA.termkey', // $query is already a query object here, so you can call func on it directly 'count' => $query->func()->count('*') ])
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

indeed that looks much better to me, but I wonder if CakePHP ORMs provides something simple like this which I'm using in Symfony QueryBuilder: $qr->select('COUNT()') which is equivalent to this statement $qr->select($qr->expr()->count('')).
You can write queries that are more raw instead of using the functional method, but they don't protect you against things like SQL injection attacks the same way.
0

Let's see what you do here:

  • you get the table locator (needed step)
  • you get the table (needed step)
  • you need to load data (needed step)
  • you need to call a function (needed step)
  • you call that count function (needed step)
Improve this answer

3 Comments

hmm... Do I really need to call a function for Count? I'm not that sure.... Symfony QueryBuilder enables me to do it in a much simple way without the function call..
@ChristianFelix you probably mean Doctrine. In this example stackoverflow.com/questions/9214471/… count is called as an SQL function. So, calling count is needed anyways, but in your code count() is available via calling func()
actually I meant dql not doctrine... In the example pointed by you there is a call: $qb->select('count(account.id).. which I would like to use...but I'm not able to apply this approach with cakephp....so the question remains the same. Does cakephp generally prohibits such statements due to some portablity issues or I'm just missing something at this point.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.