MySQL Inner Query with a Where and Group by conditions in cakephp

Question

Hi,

 i am having a table named Reports with 

 id report_id user_id 
  1     1       5
  2     1       5
  3     1       5
  4     2       5
  5     2       5
  6     3       6
  7     3       6
  8     4       1
  9     4       1
 10     4       1

i am trying to write a Query such that user_id = 5 and to find how many reports he has created.(Answer should be of 2 )

i have a Wrote a Mysql Query as

  select count(distinct report_id) from Reports where user_id=5

i m trying the same MYSQl sub Query inside the Foreach users loop where my 5 is from $user['User']['id'];

how to write the MYSQL Query above inside this for loop in cakephp Framework....

          foreach($users as & $user):

       echo "User id ".$user['User']['id'];

         $user['User']['report_count'] = $this->Report->find('count',
         array('conditions'=>array('Report.user_id'=>$user['User']['id'])));


     endforeach;

   $this->set('users', $users);

Please suggest me.......HOw to write the above Mysql Query in cakephp

Answer 1

You want to use the following functions GROUP BY and COUNT

Your query could look somewhat like this

select count(distinct report_id) from Reports where user_id=5

Answer 2

If this is a list of users you are showing in your application... you could significantly reduce the number of queries you are running. eg. for 100 users you will be running 100 queries instead you can run a single single query to extract the user_id and count of reports by each user

select count(distinct report_id) as count,user_id from Reports where user_id IN (1,2) GROUP BY user_id;

OR if you want to run seperate queries for each user

select count(distinct report_id) as count,user_id from Report where user_id=5;

Answer 3

Try this:

$user['User']['report_count'] = $this->Report->find('count',
    array( 'conditions' => array('Report.user_id' => $user['User']['id']),
        'fields' => 'DISTINCT Report.report_id'
    )
);

It should fetch all distinct report_ids for a given user_id, then count them. Basically, it should run the query:

SELECT DISTINCT report_id FROM Reports WHERE user_id=$user['User']['id']

(after substituting the value of $user['User']['id']), then count the number of rows in the result. Caveat: I don't use CakePHP in real life, I just read the documentation; your mileage may vary. As halocursed mentions, running a single SQL query on your own would be more efficient than calling find(...) for each user ID. You could also try:

$report_counts = $this->Report->find('list',
    array( 'conditions' => array('Report.user_id' => array_map(create_function('$user', 'return $user["User"]["id"];'), $users)),
        'group'  => array('Report.user_id'),
        'fields' => array('Report.user_id', 'COUNT(DISTINCT Report.report_id) AS report_count')
    )
);
foreach ($users as &$user) {
    $user['User']['report_count'] = $report_counts[$user['User']['id']];
}

However, I don't know if CakePHP will accept aggregate functions in the 'fields' parameter, and I don't know as though find('list', ...) will pick Report.user_id as the array index. If you're having problems with the latter, you could switch to a [find('all', ...)][3] call and loop over $report_counts rather than $users. I didn't take this approach because I don't know the structure of $users, such as how it's indexed.