在cakePhp双组

Question

How can I reproduce the following query with cakePhp find() and conditions?

SELECT a, SUM(bTotal) as fullTotal FROM
  (SELECT a, SUM(b) * c as bTotal FROM Table1
  GROUP BY a)
GROUP BY a

In generally how can I write nested groups in cakePhp?

Edit:

I can write it with single group by:

$results = $this->Vote->find('all', array('fields' => array('Choice.Answer', '(COUNT(Vote.id) * User.voteMultipler) AS voteTotal', 'choice_id', 'user_id'),
                                          'group' => 'choice_id, Vote.user_id',));

I would like to group and sum the result by Answer.

Answer 1

I think you subquery needs the alias to GROUP BY again

SELECT q.a, SUM(q.bTotal) as fullTotal FROM
  (SELECT a, SUM(b) * c as bTotal FROM Table1
  GROUP BY a) q
GROUP BY q.a

In cakephp

$this->YourModel->query(' SELECT q.a, SUM(q.bTotal) as fullTotal FROM
      (SELECT a, SUM(b) * c as bTotal FROM Table1
      GROUP BY a) q
    GROUP BY q.a');

OR

App::uses('AppModel', 'Model');
class MYModel extends Model {

    public function myfunction()
    {
        return $this->query(' SELECT q.a, SUM(q.bTotal) as fullTotal FROM
          (SELECT a, SUM(b) * c as bTotal FROM Table1
          GROUP BY a) q
        GROUP BY q.a'); 

    }
}