JayminsFakeAccount JayminsFakeAccount - 24 days ago 6
SQL Question

Subqueries in Cakephp 3.0

How can I Convert Mysql Query :

SELECT * , (

SELECT COUNT( * )
FROM articles
WHERE `user_id` =1
) AS total_count
FROM articles
WHERE `user_id` =1
GROUP BY user_id


into cakephp subquery?

I have tried this,

$articlescount = $this->Articles->find('all')->where(['user_id' => 1])->count();
print_r($articlescount);


Which returns me only no of count.

Answer

I have doubts that the query you are using is the best way to do what you want to achieve since it seems that the query and the subquery are returning the same value

Anyway this is how you can obtain the same exact query you asked

$q = $this->Articles->find();

$q->select([$q->func()->count('*')])
    ->where(['user_id' => 1]);

$q2 = $this->Users->find()
    ->select(['total_count' => $q])
    ->autoFields(true)
    ->where(['user_id' => 1])
    ->group(['user_id'])
    ->all();

$q is the subquery while $q2 is the actual query you want.

By the way i think that you could simply do

$q = $this->Users->find()
    ->select(['total_count' => $q->func()->count('*')])
    ->autoFields(true)
    ->where(['user_id' => 1])
    ->group(['user_id']);
Comments