doctrine querybuilder i podzapytanie z COALESCE

Mam problem z querybuilderem z doctrine. Utworzyłem zapytanie z warunkiem, w którym to chcę porównać sumy zwrócone poprzez podzapytania:

$qb = $this->createQueryBuilder('f');
...
$qb->addWhere($qb->expr()->gt(
         '(SELECT SUM(bar.amount) FROM App\Entity\Bar bar WHERE bar.foos = f.id GROUP BY bar.foo_bars),
         '(SELECT SUM(baz.amount) FROM App\Entity\Baz baz WHERE baz.foos = f.id GROUP BY baz.foo_baz)'
) 

Wszystkie działa ok, doctrine trawi to oczywiście bez problemu.

Problem pojawia się natomiast kiedy tabela Bar lub Baz nie posiada rekordu dla danego Foo. Podzapytanie zwraca wtedy NULL i nici z porównania.

Jedynym rozwiązaniem jaki mi przychodzi do głowy jest oczywiście COALESCE:

$qb->addWhere($qb->expr()->gt(
         '(COALESCE((SELECT SUM(bar.amount) FROM App\Entity\Bar bar WHERE bar.foos = f.id GROUP BY bar.foo_bars),0))',
         '(COALESCE((SELECT SUM(baz.amount) FROM App\Entity\Baz baz WHERE baz.foos = f.id GROUP BY baz.foo_baz),0))'
) 

Doctrine jednak nijak tego nie trawi zwracając:

 Error: Expected Literal, got 'SELECT'

Jak to ugryźć?

Z góry dzięki.

Tak na szybko, nie zadziała coś takiego?

SELECT COALESCE(SUM(bar.amount), 0) as amount FROM App\Entity\Bar bar WHERE bar.foos = f.id GROUP BY bar.foo_bars

Niestety nie, to rozwiązanie zamieni jedynie null na 0 jeśli SUM(bar.amount) zwróci NULL, ja muszę sprawdzić czy cały SELECT daje NULL, a dzieje się tak kiedy w tabeli Bar nie ma rekordu przyporządkowanego danemu Foo.

Niestety nie, to rozwiązanie zamieni jedynie null na 0 jeśli SUM(bar.amount) zwróci NULL, ja muszę sprawdzić czy cały SELECT daje NULL, a dzieje się tak kiedy w tabeli Bar nie ma rekordu przyporządkowanego danemu Foo.

Dodanie warunku IS NOT NULL

$qb->addWhere(sprintf('(%s)', $qb->expr()->isNotNull(
         '(SELECT SUM(bar.amount) FROM App\Entity\Bar bar WHERE bar.foos = f.id GROUP BY bar.foo_bars)'
))

kończy się błędem

 Error: Expected =, <, <=, <>, >, >=, !=, got 'IS'

Nie możesz użyć native sql?

Innym rozwiązaniem, i to bardzo dobrym z punktu widzenia wydajności, jest stworzenie triggerow które sumują amount w bar i baz dla każdego id, w momencie operacji na bazie danych - czyli zmiany w bar/baz. Masz co prawdą trochę większą baze ale samo zapytanie odczytujące sumy jest bardzo szybkie i proste w konstrukcji.

TomRZ napisał(a):

Nie możesz użyć native sql?

Mogę, ale wtedy zamiast korzystać z dobrodziejstw ORM biorę maczugę i wracam do jaskini.

TomRZ napisał(a):

Innym rozwiązaniem, i to bardzo dobrym z punktu widzenia wydajności, jest stworzenie triggerow które sumują amount w bar i baz dla każdego id, w momencie operacji na bazie danych - czyli zmiany w bar/baz. Masz co prawdą trochę większą baze ale samo zapytanie odczytujące sumy jest bardzo szybkie i proste w konstrukcji.

Jest to jakieś rozwiązanie, mogę również dodać:

class Foo 
{
....
private $total_amount;


public addFoo(Foo $foo)
{
  $this->total_amount += $foo->getAmount();
}


public removeFoo(Foo $foo)
{
  $this->total_amount -= $foo->getAmount();
}

Wtedy te same dane lądują w dwóch osobnych tabelach co jest sprzeczne z podstawami baz danych.

=========================================

Może ja źle podchodzę do całego problemu od same początku. Chodzi mi o taki oto scenariusz:

1. Mam trzy obiekty Faktura, FakturaPozycja, FakturaPlatnosc
2. Cztery table FAKTURA, FAKTURA_POZYCJA, FAKTURA_PLATNOSC, FAKTURA_PLATNOSC_2_FAKTURA (many2many - faktura może być opłacona na raty)
3. Chce pobrać tylko niepłacone faktury, czyli takie dla których nie zostały przypisane żadne płatności lub sama płatności < suma pozycji:

WHERE SELECT SUM(faktura_pozycja.warosc) IS NOT NULL 
    && SELECT SUM(faktura_platnosc.warosc)  IS NOT NULL 
    && SELECT SUM(faktura_pozycja.warosc) > SELECT SUM(faktura_platnosc.warosc)

lub 

WHERE COALESCE(SELECT SUM(faktura_pozycja.warosc),0) > COALESCE(SELECT SUM(faktura_platnosc.warosc),0)
 

Doctrine nie trawi jednak takiego czegoś jak WHERE "select subquery" IS NOT NULL lub COALESCE("select subquery", 0)

Po pierwsze, radziłbym unikać deprecjonowania natywnego SQL-a i faworyzowania ORM. To nie nie jest tak, że ORM jest zawsze najlepszym wyjściem. Z pierwszym problemem w ORM już się zetknąłeś - czyli nie pełna kompatybilność z SQL, po drugie ORMy to generalnie mniejsza wydajność zapytań do bazy, i znalazłoby się jeszcze kilka argumentów ale temat nie jest o tym.

Po drugie pisząc o triggerze miałem na mysli trigger bazodanowy w silniku bazy danych, czyli warstwie danych, a nie w warstwie aplikacji (PHP).

Co do struktury, to nie pokazałeś jej dokładnie, ale ja bym zrobił tak:

FAKTURA - dane kupującego / sprzedającego, plus: suma wszystkich pozycji, plus kwota dotychczas zapłacona.

Dodajesz/usuwasz/zmianiasz pozycje na fakturze: suma wszystkoch pozycji zmienia się

Dochodzi płatność: aktualizujesz kwote dotychczas zapłaconą.

Przy takim mechaniźmie robisz wtedy bardzo proste zapytanei aby zobaczyć faktury które jeszcze nie są całkowicie opłacone.

Do tego dodatkowa logika biznesowa w postaci np. zabronienia manipulacji pozycjami na fakturze jezeli juz wpłyneła jakaś płatność, lub tworzenie wtedy automatyc znie faktury-korekty etc.

Przy czym w momencie jak się zaczynasz bawić z triggerami po stornie DB, to jako silnik bazy danych polecam PostgreSQL który sobie z tym o wiele lepiej radzi niż MySQL.

TomRZ napisał(a):

Po pierwsze, radziłbym unikać deprecjonowania natywnego SQL-a i faworyzowania ORM. To nie nie jest tak, że ORM jest zawsze najlepszym wyjściem. Z pierwszym problemem w ORM już się zetknąłeś - czyli nie pełna kompatybilność z SQL, po drugie ORMy to generalnie mniejsza wydajność zapytań do bazy, i znalazłoby się jeszcze kilka argumentów ale temat nie jest o tym.

Dodałbym jeszcze, że Query Builder i tak potem kompiluje kod do zwykłego SQLa. Więc ja preferuję takie podejście:

1. Tam gdzie zapytanie jest proste do napisania, nie ma dziwnych warunków, z góry wiadomo, jak ma wszystko wyglądać, to używam SQLa.
2. Jeśli mamy jakieś warunki, pętle, czy inne udziwnienia, które nie pozwalają napisać nam zapytania w jednym ciągu, to wtedy używam Query Buildera.