Cakephp获得所有月份的记录与一个单一的查询，即单一的发现 - Cakephp get all months records with a single query i.e with single find

Cakephp get all months records with a single query i.e with single find

我正在与平台cakephp和使用谷歌图表报告电子商务工作。我的要求是获得所有记录为每12个月，所以我已经使用以下代码为一个月

查询

SELECT COUNT(*) AS count FROM orderproductmasters AS Orderproductmaster 
    LEFT JOIN ordermasters AS Ordermaster ON 
        (Orderproductmaster.ordermaster_id = Ordermaster.id) LEFT JOIN productmasters AS Productmaster ON 
        (Orderproductmaster.productmaster_id = Productmaster.id) 
        WHERE Ordermaster.orderstatusmaster_id = 1 AND Month(Orderproductmaster.created) = 8

 $this->Orderproductmaster->find('count',
        array('conditions'=>array('Ordermaster.orderstatusmaster_id'=>1,'
            Month(Orderproductmaster.created)'=>8)));

因为，我需要一月，二月，三月和所有12个月的记录…，所以12个月我使用以下代码

for($i=1;$i<13;$i++)
{
    $orderproductmasters[$i] = $this->Orderproductmaster->find('count',
        array('conditions'=>array('Ordermaster.orderstatusmaster_id'=>1,
            'Month(Orderproductmaster.created)'=>$i)));
}

所以问题可能是愚蠢的，但是否有可能得到所有月份的记录，而不使用for循环，即在一个查询。提前感谢

我认为在存储过程中使用游标可以满足您的需求。然后使用存储过程来cake-php.

数据库侧的例子如下

 $options = array();
    $options['fields'] = array('COUNT(Orderproductmaster.id)'); 
    $options['conditions'] = array('Ordermaster.orderstatusmaster_id = 1',
    'Month(Orderproductmaster.created) BETWEEN 1 AND 12');
    $options['joins'] = array(
                            array(
                            'table' => 'ordermasters',
                            'alias' => 'Ordermaster',
                            'type' => 'left',
                            'conditions' => array(
                                 'Orderproductmaster.ordermaster_id = Ordermaster.id'
                             )
                          ),
                        array(
                            'table' => 'productmasters',
                            'alias' => 'Productmaster',
                            'type' => 'left',
                            'conditions' => array(
                            'Orderproductmaster.productmaster_id = Productmaster.id'                
                           )
                         )
                                                        );
            $options['group'] => array('Month(Orderproductmaster.created)');    
  $this->Orderproductmaster->find('all',$options);

 $this->Orderproductmaster->find('count',
        array(
     'fields'=>'DISTINCT(Month(Orderproductmaster.created)),
     'conditions'=>array('Ordermaster.orderstatusmaster_id'=>1,'
            Month(Orderproductmaster.created)'=>8)));