CODEWITHSUNDEEP
sqlsql-server-2008cakephpfindcakephp-1.3
RSK
RSK

Reputation: 17516

Convert query into cakePHP find statement

Can any one help me to convert this query into CakePHP find statement

SELECT
    SUM(ISNULL(tg_prd_value,0)) as total,MAX(tblMarketingArea.mkt_Area_ID) as mkt_Area_ID,MAX(tblMarketingArea.mkt_Area) as mkt_Area
FROM
    tblTarget right outer join
    tblMarketingArea on (tblMarketingArea.mkt_Area_ID = tblTarget.tg_area_id and tblTarget.tg_month = 1 and tblTarget.tg_year = 2011)
where tblMarketingArea.isDeleted!=1
GROUP BY
    tblMarketingArea.mkt_Area_ID order by tblMarketingArea.mkt_Area_ID

i have "Target" and "MarketingArea" model.(I know the tablenames are not according to the cakephp convention but i have to use these names). Since i am using sql server i created Synonyms for the tables according to the cake convension

model MarketingArea (marketing_area.php)

class MarketingArea extends AppModel{
   var $name='MarketingArea';
   var $primaryKey='mkt_Area_ID';
 }

i have tried this binding

$this->bindModel(array(
        'hasMany' => array(
            'Target' => array(
                'foreignKey' => 'tg_area_id',
                'conditions' => array(
                    'tg_month' => 1,
                    'tg_year' => 2011
                    )
                                 )
                          )
                     )
                );

But still i am facing problem with SUM(tg_prd_value)

thankz in advace.

Upvotes: 0

Views: 925

Answers (1)

dhofstet
dhofstet

Reputation: 9964

Unless there is some special reason for using the find method, I would simply create a model method and use query() to execute the SQL statement. And if month and year are parameters, make sure they are integers to avoid an SQL injection attack.

Upvotes: 3

Related Questions