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