CODEWITHSUNDEEP
zend-frameworkormzend-db
StuBlackett
StuBlackett

Reputation: 3857

Zend DB Table Abstract

I've hit a bit of a brick wall in converting an SQL Query into a suitable ORM Query

The query is pretty complex even in SQL Terms, But I am looking at ways of collating this into an ORM Query, as I'm converting a lot of functions I've produced in Flex into Zend Framework Models for future proofing and API Purposes.

The current flat SQL is as follows :

SELECT subcategory_id, 
SUM(total) / (1+(YEAR(2011)*12+MONTH(1))-(YEAR(2011)*12+MONTH(12)))      
AS monthly_average FROM aggr_subcategory WHERE user_id=1 AND month BETWEEN 01 AND 05 
         GROUP BY user_id, subcategory_id

The main issues I am having are the SUM, I can manage the GROUP and WHERE clauses in ORM, but cannot get the sum in the DB Table Abstract.

Any help or points in the right direction much appreciated.

Upvotes: 3

Views: 1225

Answers (2)

Marcin
Marcin

Reputation: 238081

What about this:

    $db = Zend_Db_Table::getDefaultAdapter();

    $select = $db->select();

    $dbExpr = new Zend_Db_Expr("SUM(total) / (1+(YEAR(2011)*12+MONTH(1))-(YEAR(2011)*12+MONTH(12))) AS monthly_average");

    $select->from(
               'aggr_subcategory', 
               array('subcategory_id', $dbExpr)               
            )->where('user_id = 1')
             ->where('month BETWEEN 01 AND 05')
             ->group(array('user_id', 'subcategory_id'));


    echo $select->assemble();
    exit;

Results in:

SELECT `aggr_subcategory`.`subcategory_id`,
SUM(total) / (1+(YEAR(2011)*12+MONTH(1))-(YEAR(2011)*12+MONTH(12))) AS monthly_average 
FROM `aggr_subcategory` 
WHERE (user_id = 1) AND (month BETWEEN 01 AND 05) 
GROUP BY `user_id`, `subcategory_id

`

Upvotes: 5

adlawson
adlawson

Reputation: 6431

Although ORMs are great tools, they cannot (and shouldn't try to) entirely replace native sql. You can simply execute your sql by:

$db->query($sql);

Upvotes: 2

Related Questions