How can I convert this query in to CakePHP query?

Question

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.

Answer 1

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:

Joining tables

Retrieving Your Data

Virtual fields

Answer 2

Try to use Cake's Cookbook solution:

http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#prepared-statements