CODEWITHSUNDEEP
sqlcakephp-3.0
Jaymin
Jaymin

Reputation: 1661

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.

Upvotes: 0

Views: 1690

Answers (2)

lamasgergo
lamasgergo

Reputation: 88

Ariala's comment almost good but i think this code is more flexible because the suquery is not contains user id fixed condition:

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

$q->select([$q->func()->count('*')])
      ->where(['Articles.user_id = Users.id']);

$q2 = $this->Users->find()
         ->select(['id',  'first_name',  'total_count' => $q])
         ->where(['id' => 1])
         ->all();

But if you would like to list all users with article count you can do it if you leave the where condition from $q2.

It will results like this:

id | first_name | total_count
1  | John       | 3
2  | Doe        | 0

Upvotes: 0

arilia
arilia

Reputation: 9398

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']);

Upvotes: 1

Related Questions