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.
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');
}
}