CakePHP Querybuilder & Count Syntax

Question

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?

score 3 · Accepted Answer · edited Mar 11 '20 at 23:12

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('*')
])

edited Mar 11 '20 at 23:12

mark

21,691
3
49
71

answered Mar 11 '20 at 23:09

Greg Schmidt

5,010
2
14
35

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('')). – Christian Felix Mar 12 '20 at 10:37
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. – Greg Schmidt Mar 12 '20 at 15:36

score 0 · Answer 2 · answered Mar 11 '20 at 15:22

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)

answered Mar 11 '20 at 15:22

Lajos Arpad

64,414
37
100
175

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.. – Christian Felix Mar 12 '20 at 10:22
@ChristianFelix you probably mean Doctrine. In this example https://stackoverflow.com/questions/9214471/count-rows-in-doctrine-querybuilder count is called as an SQL function. So, calling count is needed anyways, but in your code count() is available via calling func() – Lajos Arpad Mar 13 '20 at 11:46
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. – Christian Felix Mar 13 '20 at 13:26

CakePHP Querybuilder & Count Syntax

2 Answers2