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?

user216084 · Accepted Answer · 2014-03-19 06:59:50Z

10

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()

answered Mar 19, 2014 at 6:59

user216084

Sign up to request clarification or add additional context in comments.

5 Comments

Vineet Over a year ago

Trust me it doesn't prevent SQL injection. :(

user216084 Over a year ago

@Vineet You must be talking about the raw query, right? Because when query is formulated using CakePHP, it sanitizes etc to prevent SQL injection. Also, for raw query see prepared statements at book.cakephp.org/2.0/en/models/…

Vineet Over a year ago

Yes exactly I am using prepared statements now.

user216084 Over a year ago

@Vineet Glad to know, and this discussion will help future readers also.

A.A Noman Over a year ago

This is helpful for me

Sadikhasan · Accepted Answer · 2014-03-19 06:55:08Z

6

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

answered Mar 19, 2014 at 6:55

Sadikhasan

18.6k23 gold badges85 silver badges126 bronze badges

Comments

Utsav Munendra · Accepted Answer · 2019-07-04 09:58:27Z

0

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

edited Jul 4, 2019 at 9:58

Utsav Munendra

695 bronze badges

answered May 29, 2019 at 9:25

Sanjun Dev

5189 silver badges20 bronze badges

Collectives™ on Stack Overflow

how to write SQL Query in cakephp?

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related