How can I convert this query into a CakePHP query?
SELECT COUNT(invoices.id) FROM invoices,sales
WHERE invoices.id=sales.invoice_id AND to_id='13' AND is_updated='0'
GROUP BY invoice_id
I have two controllers and two models, invoices and sales.
For aggregate functions like COUNT
, you need to create virtual field.
$this->Invoice->virtualFields['total'] = 'COUNT(`Invoice`.`id`)';
$result = $this->Invoice->find('all', array(
'fields' => array('Invoice.total'),
'joins' => array(
array(
'table' => 'sales',
'alias' => 'Sale',
'type' => 'INNER',
'conditions' => array(
'Invoice.id = Sale.invoice_id',
)
)
),
'conditions' => array(
'to_id' => 13,
'is_updated' => 0
),
'group' => array('Sale.invoice_id')
));
pr($result); // Check your result
Use the following links as references: