累积DQL与原则 [英] Cumulative DQL with Doctrine

问题描述

我很难制定一个合适的DQL来产生累积和。我可以在简单的SQL中执行，但是当涉及到DQL时，我无法控制它。

这是SQL中如何看待：

  SELECT s.name，p.date_short，p.nettobuy ，
（select sum（pp.nettobuy）as sum from price pp where pp.stock_id = p.stock_id and p.broker_id = pp.broker_id and pp.date_short< = p.date_short）as cumulative_sum 
 FROM price p 
 left join stock s on p.stock_id = s.id 
 group by p.stock_id，p.date_short 
 order by p.stock_id，p.date_short 
  

谢谢

解决方案

嘿，我检查了Doctrine 1.2的文档，创建查询的方式是（注意别名）：

  $ query = Doctrine_Query :: create（）; 
 $ query-> addSelect（'AVG（price）as price'）; 
 $ query-> addSelect（'AVG（cost）as cost'）; 
 //与你需要的
 $ query-> from（'my_table'）一样多的addSelect（）; 
  

输出创建的SQL查询：

  echo $ query-> getSqlQuery（）; 
  

执行语句：

  $ product = $ query-> fetchOne（）; 
  

访问检索到的数据是：

  echo $ product-> getPrice（）; 
 echo $ product-> getCost（）; 
  

阅读文档的其余部分 Group By Clauses 。

Im having a hard time working out a proper DQL to generate cumulative sum. I can do it in plain SQL but when it comes to DQL i cant get hold of it.

Here is how it looks in SQL:

    SELECT s.name, p.date_short, p.nettobuy,
    (select sum(pp.nettobuy) as sum from price pp where pp.stock_id = p.stock_id and p.broker_id = pp.broker_id and pp.date_short <= p.date_short) as cumulative_sum
FROM price p
    left join stock s on p.stock_id = s.id
    group by p.stock_id, p.date_short
    order by p.stock_id, p.date_short

Thanks

解决方案

Hey, I have check the documentation for Doctrine 1.2, and the way to create the query is (put attention on the alias):

$query = Doctrine_Query::create();
$query->addSelect('AVG(price) as price');
$query->addSelect('AVG(cost) as cost');
// as many addSelect() as you need
$query->from('my_table');

To output the SQL query created:

echo $query->getSqlQuery();

To execute the statement:

$product = $query->fetchOne();

And to access the retrieved data is:

echo $product->getPrice();
echo $product->getCost();

Read the rest of the documentation at Group By Clauses.

这篇关于累积DQL与原则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！