DQL выберите все строки, имеющие максимальное значение для одного столбца

Работая с Symfony 2 и Doctrine, я ищу способ выбрать все строки, имеющие максимальное значение в определенном столбце.

Прямо сейчас я делаю это в двух запросах:

  • Один, чтобы получить максимальное значение столбца в таблице
  • Затем я выбираю строки, имеющие это значение.

Я уверен, что это можно сделать с помощью одного запроса.

Ища, я нашел этот ответ в потоке , который кажется тем, что я ищу, но в язык SQL.

Итак, согласно первому решению ответа, запрос, который я пытаюсь построить, будет примерно таким: 

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

Кто-нибудь знает, как сделать это в доктрине 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')

            // 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, поэтому этот код находится в методе соответствующего репозитория.

В более общем плане я изучаю, как обрабатывать подзапросы (SQL & DQL) и синтаксис DQL для advanced запросы.

ТХ!

3 4
symfony php sql-server greatest-n-per-group doctrine2

3 ответа:

После нескольких часов головной боли, поиска в гугле и чтения stackOverflow... Наконец-то я узнал, как это делается.

Вот мой окончательный код DQL queryBuilder: 

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

Для меня, когда я пытаюсь сделать подзапрос, я делаю: 

->andWhere($qb->expr()->eq('affaire', $qb2->getDql()));
0

Чтобы достичь этого, используя чистый DQL и без использования какой-либо агрегатной функции, вы можете написать запрос доктрины как 

SELECT a
FROM GAAffairesBundle:MontantMarche a
    LEFT JOIN GAAffairesBundle:MontantMarche b
    WITH a.affaire = b.affaire 
    AND a.periodeComptable < b.periodeComptable
WHERE b.affaire IS NULL
ORDER BY a.periodeComptable DESC

Вышеописанное вернет вам максимальную запись на группу (на affaire)

Expalnation

Эквивалентный SQL для вышеуказанного DQL будет выглядеть как 

SELECT a.*
FROM MontantMarche a
LEFT JOIN MontantMarche b 
    ON a.affaire = b.affaire 
    AND a.periodeComptable < b.periodeComptable
WHERE b.affaire IS NULL
ORDER BY a.periodeComptable DESC

Здесь я предполагаю, что в таблице может быть несколько записей, например (MontantMarche) для каждого affaire, поэтому здесь я пытаюсь сделать самосоединение на affaire , а другая настройка в join-это я попытка объединить только строки из правой таблицы (b) , где a-periodeComptable (a) с наибольшим значением periodeComptable будет иметь нулевую строку из правой таблицы(b), таким образом, чтобы выбрать самую высокую строку для affaire WHERE right table row IS NULL.

Аналогично, используя ваш опубликованный пример запроса с внутренним соединением, можно записать как 

select yt.id, yt.rev, yt.contents
from YourTable yt
left join YourTable ss on yt.id = ss.id and yt.rev < ss.rev
where ss.rev is null

Надеюсь, это имеет смысл

0