how to write SQL Query in cakephp?

Question

select campaign,lead_status,COUNT(id)
from buyers 
where lead_status IN('Very Hot','Hot', 'Warm', 'Cold', 'Not Contacted') 
    and campaign IN('stage1','stage2','stage3','stage4') and created_on >='2012-10-01' 
    and is_put_for_exchange='0' and transaction_type='First Sale' 
    and propertytype='Residential' 
group by campaign,lead_status 
ORDER BY campaign asc, field(lead_status,'Very Hot','Hot', 'Warm', 'Cold', 'Not Contacted')

convert in cakephp Syntax plz It Argent?

Answer 1

Try this

$this->Buyer->find('all', array(
'fields' => $arrayOfFields,
'conditions' => array(
    'Buyer.lead_status' => $arrayOfLeadStatus,
    'Buyer.compaign' => $arrayOfCompaign,
    'Buyer.is_put_for_exchange' => 0,
    'Buyer.transaction_type' => 'First Sale',
    'Buyer.propertytype' => 'Residential'   
    ),
'group' => array('Buyer.campaign','Buyer.lead_status'),
'order' => 'Buyer.campaign'
));

Answer 2

For reference to others, this query will be look like this if we convert into CakePHP method

$result = $this->ModelName->find("all",array(
    'fields' => array('campaign','lead_status','count(id') as counts),
    'conditions' => array(
        'lead_status' => array('Very Hot','Hot', 'Warm', 'Cold', 'Not Contacted'),
        'campaign' => array('stage1','stage2','stage3','stage4'),
        "created_on >='2012-10-01' ",
        'is_put_for_exchange' => 0,
        'transaction_type' => 'First Sale',
        'propertytype' => 'Residential'
    ),
    'group' => array('Buyer.campaign','Buyer.lead_status'),
    'order' => 'Buyer.campaign'
));

Answer 3

You can modify your raw SQL into CakePHP format by going through the documentation.

Or for any reason you cannot / will not, then you can use the raw SQL query as follows:

In controller:

$this->ModelName->query('SELECT * FROM table');

In model:

$this->query('SELECT * FROM table');

Documentation of query()