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?

mark · Accepted Answer · 2020-03-11 23:12:49Z

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, 2020 at 23:12

mark

21.8k3 gold badges54 silver badges75 bronze badges

answered Mar 11, 2020 at 23:09

Greg Schmidt

5,1482 gold badges17 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Christian Felix Over a year ago

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('')).

Greg Schmidt Over a year ago

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.

Lajos Arpad · Accepted Answer · 2020-03-11 15:22:19Z

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, 2020 at 15:22

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

3 Comments

Christian Felix Over a year ago

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..

Lajos Arpad Over a year ago

@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()

Christian Felix Over a year ago

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.

Collectives™ on Stack Overflow

CakePHP Querybuilder & Count Syntax

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related