Zend DB Table Abstract

Question

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.

Is `Zend_Db_Expr` what you are looking for? – David Weinraub Jul 13 '11 at 13:34 — David Weinraub, Jul 13 '11 at 13:34

Marcin · Accepted Answer · 2011-07-13T14:35:16.440

5

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

`

edited Jul 13 '11 at 14:35

answered Jul 13 '11 at 14:26

Marcin

215,873
14
235
294

Excellent! Works a treat, thanks for that. I'll take a look at Zend_DB_Expr in future! Out of curiousity would you reccomend ORM or static SQL for Api'able projects? – StuBlackett Jul 14 '11 at 08:29
1

@StuBlackett. Glad I could help. I think it depends on your particular project. If you have bunch of complicated SQL queries, it might be faster and easier to use them, rather than translate everything into ORM. – Marcin Jul 14 '11 at 10:53

score 2 · Answer 2 · answered Jul 13 '11 at 13:40

2

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

answered Jul 13 '11 at 13:40

adlawson

6,303
1
35
46

Zend DB Table Abstract

2 Answers2