Cumulative DQL with Doctrine

Question

I find it difficult to find the correct DQL to generate a cumulative sum. I can do this in plain SQL, but when it comes to DQL, I can't hold it.

Here's what it looks like 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 

code>

thanks

Answer 1

Hey, I checked the documentation for Doctrine 1.2, and the way to create the request (note 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 generated SQL query:

 echo $query->getSqlQuery(); 

To execute a statement:

 $product = $query->fetchOne(); 

And to access the received data:

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

Read the rest of the documentation in Group By Clauses .

Answer 2

You simply specify the amount in the selected part of DQL:

 $query = Doctrine_Query::create() ->select('sum(amount)') ->from('some_table'); 

For more information, see this page in the Doctrine documentation.