DQL选择每列具有一列MAX值 [英] DQL Select every rows having one column's MAX value
问题描述
使用Symfony 2和Doctrine,我正在寻找一种方法来选择在特定列中具有最大值的每一行。
现在,在两个查询中执行此操作:
- 获取表中列的最大值的一个
- 然后我选择有这个值的行。
我确信这可以通过一个查询完成。 b
$ b
搜索,我发现这个答案在线程,这似乎是我正在寻找的,但在SQL
所以根据答案的第一个解决方案,我正在尝试构建的查询将是这样的:
从yourTable中选择yt.id,yt.rev,yt.contents
内部连接(
select id,max(rev)rev
from YourTable
group by id
)ss o n yt.id = ss.id和yt.rev = ss.rev
有人知道如何在Doctrine DQL中使用?
现在,这里是我的测试代码(不工作):
$ qb2 = $ this-> createQueryBuilder('ms')
- > select('ms,MAX(m.periodeComptable)maxPeriode')
- > where('ms.affaire =:affaire')
- > setParameter('affaire',$ affaire);
$ qb = $ this-> createQueryBuilder('m')
- > select('m')
//->where('m.periodeComptable = maxPeriode')
//这是我以为是最合理的方法:
- > innerJoin('GAAffairesBundle:MontantMarche mm,MAX(mm.periodeComptable)maxPeriode ','mm','WITH','m.periodeComptable = mm.maxPeriode')
//这是一个尝试使用另一个查询($ qb2)作为子查询的版本,这将是更好的
//因为我已经在这里使用这个子查询
// - > innerJoin($ qb2-> getDQL(),'sub','WITH',' m.periodeComptable = sub.maxPeriode')
//另一个奇怪的尝试混合DQL和SQL逻辑:/
// - > innerJoin('SELECT MontantMarche mm,MAX(mm.periodeComptable )maxPeriode ON m.periodeComptable = mm.maxPeriode','sub')
// - > ; groupBy('m')
- > andWhere('m.affaire =:affaire')
- > setParameter('affaire',$ affaire);
return $ qb-> getQuery() - > getResult();
实体是GAAffairesBundle:MontantMarche,所以这段代码在相应的仓库的方法中。 p>
更一般来说,我正在学习如何处理高级查询的子查询(SQL& DQL)和DQL语法。
Thx!
经过几个小时的头痛和谷歌搜索和stackOverflow读取...
I最后找到如何使它。
这是我最后的DQL queryBuilder代码:
code> $ qb = $ this-> createQueryBuilder('a');
$ qb2 = $ this-> createQueryBuilder('mss')
- > select('MAX(mss.periodeComptable)maxPeriode')
- > where('mss.affaire = a')
;
$ qb - > innerJoin('GAAffairesBundle:MontantMarche','m','WITH',$ qb-> expr() - > eq('m.periodeComptable' '$ qb2-> getDQL()。')'))
- >其中('a =:affaire')
- > setParameter('affaire',$ affaire)
;
return $ qb-> getQuery() - > getResult();
Working with Symfony 2 and Doctrine, I'm searching for a way to select every rows having the max value in a specific column.
Right now, I'm doing it in two queries:
- One to get the max value of the column in the table
- Then I select rows having this value.
I'm sure this can be done with one query.
Searching, I have found this answer in a thread, that seems to be what I am searching for, but in SQL.
So according to the answer's first solution, the query I'm trying to build would be something like that:
select yt.id, yt.rev, yt.contents
from YourTable yt
inner join(
select id, max(rev) rev
from YourTable
group by id
) ss on yt.id = ss.id and yt.rev = ss.rev
Does anybody know how to make it in Doctrine DQL?
For now, here is the code for my tests (not working):
$qb2= $this->createQueryBuilder('ms')
->select('ms, MAX(m.periodeComptable) maxPeriode')
->where('ms.affaire = :affaire')
->setParameter('affaire', $affaire);
$qb = $this->createQueryBuilder('m')
->select('m')
//->where('m.periodeComptable = maxPeriode')
// This is what I thought was the most logical way of doing it:
->innerJoin('GAAffairesBundle:MontantMarche mm, MAX(mm.periodeComptable) maxPeriode', 'mm', 'WITH', 'm.periodeComptable = mm.maxPeriode')
// This is a version trying with another query ($qb2) as subquery, which would be the better way of doing it for me,
// as I am already using this subquery elsewhere
//->innerJoin($qb2->getDQL(), 'sub', 'WITH', 'm.periodeComptable = sub.maxPeriode')
// Another weird try mixing DQL and SQL logic :/
//->innerJoin('SELECT MontantMarche mm, MAX(mm.periodeComptable) maxPeriode ON m.periodeComptable = mm.maxPeriode', 'sub')
//->groupBy('m')
->andWhere('m.affaire = :affaire')
->setParameter('affaire', $affaire);
return $qb->getQuery()->getResult();
The Entity is GAAffairesBundle:MontantMarche, so this code is in a method of the corresponding repository.
More generally, I'm learning about how to handle sub-queries (SQL & DQL) and DQL syntax for advanced queries.
Thx!
After some hours of headache and googling and stackOverflow readings... I finally found out how to make it.
Here is my final DQL queryBuilder code:
$qb = $this->createQueryBuilder('a');
$qb2= $this->createQueryBuilder('mss')
->select('MAX(mss.periodeComptable) maxPeriode')
->where('mss.affaire = a')
;
$qb ->innerJoin('GAAffairesBundle:MontantMarche', 'm', 'WITH', $qb->expr()->eq( 'm.periodeComptable', '('.$qb2->getDQL().')' ))
->where('a = :affaire')
->setParameter('affaire', $affaire)
;
return $qb->getQuery()->getResult();
这篇关于DQL选择每列具有一列MAX值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!