累积DQL与原则 [英] Cumulative DQL with Doctrine
本文介绍了累积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屋!
查看全文