Zend DB 表摘要

发布于 2024-11-19 23:51:11 字数 496 浏览 3 评论 0原文

我在将 SQL 查询转换为合适的 ORM 查询时遇到了一些困难

即使在 SQL 术语中查询也相当复杂，但我正在寻找将其整理为 ORM 查询的方法，因为我正在将我在 Flex 中生成了许多函数到 Zend 框架模型中，以供未来验证和 API 用途。

当前的平面SQL如下：

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

我遇到的主要问题是SUM，我可以管理ORM中的GROUP和WHERE子句，但无法在DB表摘要中获取总和。

非常感谢任何帮助或指出正确方向。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嗼ふ静 2024-11-26 23:51:11

怎么样：

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

结果是：

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

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

回复收藏 0 原文