DQL选择具有一列MAX值的每一行

2022-01-03 00:00:00 php symfony doctrine-orm sql-server greatest-n-per-group

使用 Symfony 2 和 Doctrine，我正在寻找一种方法来选择在特定列中具有最大值的每一行.

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:

  • 一个获取表中列的最大值
  • 然后我选择具有此值的行.

我确信这可以通过一个查询来完成.

I'm sure this can be done with one query.

搜索，我在 这个答案7745609/sql-select-only-rows-with-max-value-on-a-column">一个线程，这似乎是我要搜索的内容，但在 SQL 中.

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

有人知道如何在 Doctrine DQL 中制作它吗?

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();

实体是GAAffairesBundle:MontantMarche，所以这段代码在对应仓库的一个方法中.

The Entity is GAAffairesBundle:MontantMarche, so this code is in a method of the corresponding repository.

更一般地说，我正在学习如何处理高级查询的子查询(SQL 和 DQL)和 DQL 语法.

More generally, I'm learning about how to handle sub-queries (SQL & DQL) and DQL syntax for advanced queries.

谢谢！

推荐答案

经过几个小时的头痛、谷歌搜索和 stackOverflow 读数后...我终于知道怎么做了.

After some hours of headache and googling and stackOverflow readings... I finally found out how to make it.

这是我最终的 DQL queryBuilder 代码:

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();

相关文章