Query Builder documentation examples without ORM

Site Feedback
1

Hello you guys

In my slim project, I’m using the QueryBuilder for database queries (require “cakephp/database”) but nothing else from the ORM or CakePHP.

Now, on multiple occasions I read your documentation because I didn’t know how to do something specific (in my case right now it’s subqueries) but the only examples shown are using entities specific methods like getAssociation() and find().

As I’m not so experienced, it’s difficult for me to translate that into pure query builder code.

I would greatly appreciate such an example in Query Builder - 4.x#subqueries for instance.

This wasn’t supposed to be a “need help” question but if anyone wants to help me I’m trying to build this query:

select id, first_name, surname,
       (select count(u2.first_name) duplicate
        from user u2
        where u1.first_name = u2.first_name) duplicate_amount
from user u1

I have tried

$subQuery = $this->connection->newQuery();
$query = $this->connection->newQuery()->select(['id', 'first_name', 'surname',
    'duplicate_amount' => $subQuery->select(['duplicate' => $subQuery->func()->count('u2.first_name')])
        ->from(['u2' => 'user'])->where(['u1.first_name' => 'u2.first_name'])
])->from(['u1' => 'user']);

But strangely, the u2.first_name in the sub query where clause gets replaced by a named parameter :c0 except that everything looks good:

SELECT `id`, `first_name`, `surname`,
       (SELECT (COUNT(u2.first_name)) AS `duplicate`
        FROM `user` `u2`
        WHERE `u1`.`first_name` = :c0) AS `duplicate_amount`
FROM `user` `u1`
2

It’s trying to find a record where the first name is the literal string “u2.first_name”, like if you were trying to find someone whose first name was Samuel you’d use where(['u1.first_name' => 'Samuel']). You should be able to just use where(['u1.first_name = u2.first_name']). Or I think there’s a way to tell it that it’s a literal, not a variable, but I can’t remember how that works off the top of my head…

3 
'u1.first_name' => $query->identifier('u2.first_name')

or

'u1.first_name' => new \Cake\Database\Expression\IdentifierExpression(
    'u2.first_name'
)

or

where(function (
    \Cake\Database\Expression\QueryExpression $exp,
    \Cake\Database\Query $query
) {
    return $exp->equalFields('u2.first_name', 'u2.first_name');
})
4

Ohh nice suggestions, thanks!

I had solved it the following way because I remembered that I once already needed the newExpr function for a function that the query builder didn’t have directly:

->where([$subQuery->newExpr('u1.first_name = u2.first_name')])

But I guess $query->identifier('u2.first_name') is a lot cleaner for this situation.

To prevent further questions in this style, query builder examples without ORM in the docs are greatly appreciated by me :slight_smile: