Chyba v metodě page uvnitř Exploreru

Polki
Člen | 500
+
0
-

Ahoj
dnes jsem narazil na takový celkem zajímavý problém. Použil jsem cca tento kód:

$catalogues = $this->database->table('catalog')
	->alias(':catalog_x_category', 'catalogXCategory')
	->where('catalogXCategory.catalog_category_id', $categoryIds)
	->group('catalog.id');

Jde o to, že záznam katalogu může být ve vícero kategoriích, ale já chci vypsat jen záznamy, které jsou v určité kategorii.

To GROUP je tam proto, že pokud mají všechny záznamy 2 kategorie, tak díky fungování MySQL, které používám se v databázi udělá sloučení tabulek, takže výsledek je 2× tolik řádků, což vyústí v to, co nechci.
Příklad:
tabulka catalog:

ID, Name
1, A
2, B
3, C
4, D

Tabulka catalog_x_category:

ID, catalog_id, catalog_category_id
1, 1, 1
2, 1, 2
3, 2, 1
4, 2, 2
5, 3, 1
6, 3, 2

a tento dotaz:

$catalogues = $this->database->table('catalog')
	->alias(':catalog_x_category', 'catalogXCategory')
	->where('catalogXCategory.catalog_category_id', [1, 2]);

by vrátil něco jako:

catalog.ID, catalog.Name, catalog_x_category.ID, catalog_x_category.catalog_id, catalog_x_category.catalog_category_id
1, A, 1, 1, 1
1, A, 2, 1, 2
2, B, 3, 2, 1
2, B, 4, 2, 2
3, C, 5, 3, 1
3, C, 6, 3, 2

Proto tam mám přidáno to group, takže tento dotaz:

$catalogues = $this->database->table('catalog')
	->alias(':catalog_x_category', 'catalogXCategory')
	->where('catalogXCategory.catalog_category_id', [1, 2])
	->group('catalog.id');

už vrátí správně:

catalog.ID, catalog.Name, catalog_x_category.ID, catalog_x_category.catalog_id, catalog_x_category.catalog_category_id
1, A, 1, 1, 1
2, B, 3, 2, 1
3, C, 5, 3, 1

Problém nastává, když vezmu svůj dotaz s GROUP a zavolám nad ním metodu page
Page totiž právě to moje group odstraní a udělá dotaz na početprvků bez něj, což se bude rovnat číslu 6
Takže když budu volat něco takového:

$maxPage = 1;
$catalogues = $this->database->table('catalog')
	->alias(':catalog_x_category', 'catalogXCategory')
	->where('catalogXCategory.catalog_category_id', [1, 2])
	->group('catalog.id')
	->page(1, 2, $maxPage);

tak v proměnné $catalogues budou správně uloženy zgrouplé záznamy pro danou page, ale v proměnné $maxPage bude nesprávně uložená hodnota 3, protože page ve svém dotazu zruší group a tedy počet prvků (6) děleno počtem prvků na stránku (2) se rovná 3, což má za následek, že na 1 straně se mi správně vypíše záznam A a záznam B, na straně 2 se správně vypíše jen 1 záznam C, ale bohužel paginátor zprostředkoval kvůli špatnému chování ještě stranu 3, která je bohužel prázdná.

Chápu, že to je kvůli tomu, aby se ušetřil přenos po síti a výpočet db a tak se odebírají třeba order clausule, ale u té Group to má za následek to, že se page metoda potom nechová správně.
Je to chyba, nebo to je takto schválně a mám si zgroupnuté záznamy paginovat sám jinak?

EDIT 1:
Verze „nette/database“: „^3.1.3“,

Editoval Polki (21. 11. 19:34)

Kamil Valenta
Člen | 480
+
0
-
$catalogues = $this->database->table('catalog')
	->select('DISTINCT catalog.id, catalog.Name')
	->alias(':catalog_x_category', 'catalogXCategory')
	->where('catalogXCategory.catalog_category_id', [1, 2]);

?
Pokud tam teda nepotřebuješ ty sloupce z catalog_x_category (tipuji že ne, když se libovolné vezmou a libovolné zahodí).

Metoda page(), pokud se tak skutečně děje, by ovšem neměla zahazovat group().

Polki
Člen | 500
+
0
-

Kamil Valenta napsal(a):

$catalogues = $this->database->table('catalog')
	->select('DISTINCT catalog.id, catalog.Name')
	->alias(':catalog_x_category', 'catalogXCategory')
	->where('catalogXCategory.catalog_category_id', [1, 2]);

?
Pokud tam teda nepotřebuješ ty sloupce z catalog_x_category (tipuji že ne, když se libovolné vezmou a libovolné zahodí).

Metoda page(), pokud se tak skutečně děje, by ovšem neměla zahazovat group().

To stejně nepomůže, jelikož Nette ten dotaz na ten počet uvnitř metody page dělá tak, že přepíše tvůj select svým, který vypadá takto: SELECT COUNT(*) tedy ten můj DISTINCT se zahodí a výsledek je stejně špatný, jako při použití group.

Navíc chci nechat výběr použitých sloupců v SELECT na Nette, jelikož ten dotaz skládám dynamicky a kdybych to měl výčtem, tak bych buď musel posílat do všech metod jaké sloupce zrovna chci, nebo bych musel tahat všechny pomocí ->select('DISTINCT catalog.id, catalog.Name, catalog.column3, catalog.column4', ...), což mi přijde nepraktické, jelikož se bude tahat zbytečně hodně dat, když nevyužiju všechny a také při přidání nového sloupce, který chci využít bych měnil více míst, než jen výpis.
Nette Explorer mi naopak vždy při druhém refreshi stránky zajistí, že si vytáhne jen ty sloupce, co pro danou akci na stránce potřebuju, takže to je za mě OK. Proto jsem se vydal cestou group, jelikož select mám čistý pro Nette a zároveň by měla fungovat page metoda, jelikož přepisovat by se měl jen select. Asi jsem se ale spletl…

Editoval Polki (21. 11. 21:04)

Kamil Valenta
Člen | 480
+
+2
-

Metodě count() se dá předhodit, podle čeho počítat, takže tam by se ten DISTINCT procpal. Ale u metody page() ta možnost není. Leda si počet zjistit countem a stránkovat limitem víc „ručně“.

Nebo si to rozdělit na 2 selectiony, pokud je potřeba zůstat u dynamického selectu:

$catalogues = $this->database->table('catalog')
    ->where('id', $this->database->table('catalog_x_category')
        ->where('catalog_category_id', [1,2])
        ->select('catalog_id'))

Ale asi to bude o ždibec pomalejší než JOIN. Ovšem funguje to i s page().

Editoval Kamil Valenta (21. 11. 23:23)

Polki
Člen | 500
+
0
-

Jo no. Ale dělat 2 dotazy se mi kvůli tomu nechce. Spíš sáhnu po pohledu.

Co na to @DavidGrudl Je to feature, nebo bug?

Kamil Valenta
Člen | 480
+
0
-

Proč? Explorer je takto stavěný a funguje tak na mnoha místech (related třeba).
https://doc.nette.org/…ase-explorer#…

Když sáhneš po pohledu, přeneseš logiku dotazování do databáze.
Navíc ten dotaz nebude jednoduše rozšířitelný, musíš různě to sql skládat a proč to dělat ručně, když to za tebe už pomocí předpřipravených funkcí dělá explorer?

Bulldog
Člen | 14
+
0
-

@KamilValenta

Proč? Explorer je takto stavěný a funguje tak na mnoha místech (related třeba).

To si trochu pleteš. Explorer dělá efektivní dotazy. Proto někdy udělá join a někdy více dotazů.

U related konkrétně se dělají 2 dotazy proto, že si nejdřív člověk vytáhne nějaké prvky z databáze a následně nad nimi iteruje.

  • V tomto případě můžeš říct, že hloupý systém by pro každý prvek udělal při related dotaz do databáze. To je ale na prd, jelikož je složitost počtu dotazů O(n+1).
  • Chytrý zase můžeš říct, že si rovnou udělá na začátku join a natáhne prvky i jejich related, aby to nemusel dělat později. No jo, ale co když v určitý případ, když třeba jinak vyjde nějaký IF, nebo při signáu nebudeš chtít ty related prvky vykreslovat? Pak jsi je tahal zbytečně a mohl jsi tedy tahat sice 1 dotazem, ale tuny a tuny prvků, které pak zahodíš.
  • Optimální řešení je tedy vytáhnout jen dané prvky (1 dotaz) a počkat, až budeš potřebovat related prvky a ty pak vytáhnout všechny naráz (2 dotaz) tím pádem je aplikace lazy, jelikož se dotazuje jen na to, co opravdu potřebuje a dělá jen nejnutnější dotazy a složitost má tedy O(2)

V některých případech je taky lepší udělat 2 dotazy, jelikož může nastat situace, kdy JOIN bude trvat hrozně dlouho, takže výčet prvků a 2 dotazy jsou rychlejší.

Pokud jde ale o filtrování hodnotou z jiné tabulky, tak se jeví rychlejší udělat vždy jen 1 dotaz, protože se stejně vždy ty případné 2 dotazy provedou za sebou a ten load, který je ohledně navázání spojení, toku dat po síti a buildění objektů/parsování dat, které se pak stejně zahodí je většinou větší, než čas potřebný pro JOIN.

Když sáhneš po pohledu, přeneseš logiku dotazování do databáze.
Navíc ten dotaz nebude jednoduše rozšířitelný, musíš různě to sql skládat a proč to dělat ručně, když to za tebe už pomocí předpřipravených funkcí dělá explorer?

Osobně teda nevím, v čem by @Polkimu pomohl pohled v tomto případě, protože tam nemá jak jednoduše dostat catalog_category_id tak, aby měl vždy jen jeden řádek z tabulky catalog, aby s tím uměla metoda page pracovat bez groupu, i bez DISTINCT, ale můžu říct, že pohledy se občas hodí.

Například při specifických požadavcích, kdy chceš zvýšit performance dotazu například u nějakých statistik. Jako příklad může být seznam poboček firmy, kde chceme vidět výdaje za poslední měsíc, příjmy za poslední měsíc, výdaje na platy, výdaje na nákup zboží atd. To bývají složité dotazy s mnoha joinama/subqueries a skládat takto jeden specifický dotaz, který bude vždy stejný, takže je jednoúčeový v Nette je sice fajn, ale jelikož ho seskládáš jednou a pak už neměníš, jen třeba sortíš výsledky, tak se jeví rozumně udělat pohled. Dotaz, co ti vygeneruje Explorer mrskneš do pohledu a s tím už v Exploreru pracuješ zase jako s nějakou novou tabulkou, kterou můžeš sortit a dokonce i spojovat s dalšími. Sice nad ní nefunguje Insert, ale to pal čert. Důležité je, že složitá série joinů/několika dotazů je uzavřená v pohledu, který se jeví jako tabulka (a třeba v Postgresu jí opravdu je), což u jednoúčelových dotazů sníží zátěž sítě a zrychlí aplikaci. Takže v takových případech bych klidně i já sáhnul po pohledu.
Navíc je tam menší pravděpodobnost, že další modifikování už tak složitého dotazu něco rozbije, jelikož nové dotazy děláš nad výsledkem, nenapojuješ je na jiný dotaz.

Otázka je, jestli je ok používat pohled jen proto, že je v Exploreru chyba.

Fuj. tak jsem se jednou taky rozepsal.. :D

Kamil Valenta
Člen | 480
+
-1
-

@Bulldog promiň, nezdůraznil jsem, že to nejsou má slova, ale že jsem citoval Polkiho, já přednosti pohledů znám. I to catalog_category_id do pohledu dostaneš, tedy pokud sáhneš po metodě query.

Zatím z uvedeného se zdá úplně v pohodě nechat Explorer položit 2 dotazy.

Bulldog
Člen | 14
+
-1
-

@KamilValenta
Necitoval. Já to četl. @Polki mluvil o triggerech a o procedurách, které na pozadí mění data a nebo dělají nějaké skryté věci, které z aplikace nejsou transparentně vidět. To je podobné jako antipattern o skrytých závislostech, když ti update jedné hodnoty úplně změní hodnotu v jiné části databáze, kde to nečekáš, protože se tví databázoví specialisti rozhodli, že si tam něco přidají.

Pohledy jsou úplně jiná věc, která se stará o zobrazení dat a nijak by je neměly měnit. Proto jsou v krajních případech pochopitelné. Ostatně jak @Polki napsal:

Pokud máš opravdu potřebu cpát nějaké algoritmy do pohledů, tak je to úplně jedno, protože s pohledy si Explorer poradí a to jak se ten pohled na pozadí vytvoří, jestli jen nějakým selectem, nebo na to máš proceduru nikoho nezajímá.

a dále:

No a když jde o pohledy, tak tam taky potom záleží, jaký DB systém používáš. Dělat pohledy třeba nad MySQL/MariaDB a ještě se stored procedurama je nerozum, protože se to stejně spustí vždy znovu, takže pak je jedno, jestli to máš na straně databáze, nebo na serveru.

Tady sice píše, že nad MySQL je to nerozum, jelikož se to stejně vždy spouští znovu a že je tedy jedno, odkud se ten SQL dotaz zavolá, ale jde o to, že Pohledy nezavrhl. Proto chápu, že v krajních případech, kdy jde o jednoúčelový dotaz, který chce udělat proto, aby ušetřil load sítě, jelikož udělat to v jednom dotazu mu Nette nedovolí, ale chce nadále používat jen Explorer je naprosto v souladu se vším, co napsal.
Možná by si měl dřív, než se k něčemu začneš vyjadřovat si problematiku nastudovat, pochopit a pak teprve něco napsat. Pak by to chtělo trochu taky trénovat paměť a v neposlední řadě se naučit, že problémy se mají řešit a ne obcházet. Pokud se někdo zeptá, jestli problém co má je chyba, tak odpověď by měla být ANO, nebo NE.
Návrh jak případnou chybu obejít a ještě neefektivně jen aby sis zvedl sebevědomí tím, že ho potopíš nesmyslnými argumenty z nějaké bývalé konverzace, jelikož jsi stále z ní nejspíš ublížený není předmětem diskuse.

Kamil Valenta
Člen | 480
+
+2
-

Bulldog napsal(a):

@KamilValenta
Necitoval. Já to četl.

Tak jsi to četl špatně. Dej si Ctrl+C, Ctrl+F, Ctrl+V.

že problémy se mají řešit a ne obcházet.

Jenže ono neplatí, že dva dotazy jsou vždycky horší než jeden join následně grupovaný. A rozumný test se z dostupných informací udělat nedá. Takže je dost těžké říct, zda je to řešení nebo obcházení.

Návrh jak případnou chybu obejít a ještě neefektivně jen aby sis zvedl sebevědomí tím, že ho potopíš nesmyslnými argumenty z nějaké bývalé konverzace, jelikož jsi stále z ní nejspíš ublížený není předmětem diskuse.

Totálně mimo. Poslal jsem mu řešení, které pokrývá jeho dotaz a je dle Nette dokumentace. On to zavrhl a já se jeho slovy zeptal proč. Nic víc v tom nehledej.

Bulldog
Člen | 14
+
+1
-

Dobře máš pravdu.

filsedla
Člen | 101
+
+1
-

Ahoj. Zaujalo mě, že si nepamatuju, že bych na problém narazil – a to v podstatě nepoužívám pohledy ani group(), tak jsem pátral v kódu.

Pro paginaci se používá Nette\Utils\Paginator nebo VisualPaginator. Tomu je potřeba dodat celkový počet prvků (a stránku a počet prvků na stránku) a on vrátí parametry do limit().

Volám tedy limit() ručně. Stejně tak samostatným dotazem count().

A ten, v relativně vzácném případě, kdy select obsahuje backjoin, musí navíc obsahovat DISTINCT.

$count = $this->database->table('product')->count('DISTINCT product.id');

Dle mého je metoda page() taková vychytávka, jejíž funkcionalita v tomto případě nestačí jelikož natvrdo volá count('*'). Za nejjednodušší proto považuju nepoužít page() a dělat to, jak jsem popsal výš.

Edit: PS: Select dotaz, kde je limit(), musí obsahovat DISTINCT také. Takže to nejde dohromady s automatickým selectem sloupců, původně jsem myslel, že jo. Celý kód pak vypadá zhruba takto:

$sProduct = $this->database->table('product')
    ->select('DISTINCT product.*')
    ->where(...); // Tady může být backjoin

$count = $sProduct->count('DISTINCT product.id');

$paginator = new Paginator();
$paginator->setItemsPerPage(...);
$paginator->setItemCount($count);

$sProduct->order(...)->limit($paginator->length, $paginator->offset);

Editoval filsedla (24. 11. 16:50)

Polki
Člen | 500
+
+1
-

@filsedla Díky za pěkný komentář.
Já jsem si udělal vlastní paginátor, který bere na vstup Selection a s tou dále pracuje (volá nad ní zmíněnou metodu page a vrací zpaginované prvky). Pro moje použití to dostačuje.

Vidím ale, že tam nemůžu posílat selection, která má v sobě GROUP. Nejspíš to bude kvůli specifickému fungování COUNT nad zgrupnutými záznamy.

Z nabitých informací usuzuji, že nejvhodnější řešení pro tento problém by mohlo být řešení, co popisoval @KamilValenta a to mrsknout tam dva dotazy, díky kterým nebudu muset používat join.

Alternativně můžu použít i to, co jsi psal ty s tím, že místo page použiju něco jako ->count('mujDotaz'), abych se problémům vyhnul, ale aktuálně mi přijde jednodušší na implementaci udělat 2 dotazy.

EDIT 1:
Udělal jsem to nakonec tak, že jsem si nechal Explorerem seskládat ten vnitřní dotaz, vygenerovat SQL kód a ten jsem pak pomocí SqlLiteralu vložil jako parametr do wheru toho původního dotazu, takže výsledný SQL vypadá takto:

SELECT COUNT(*)
FROM `catalog`
WHERE (`id` IN (
	SELECT DISTINCT `catalog_id`
	FROM `catalog_x_category`
	WHERE (`catalog_category_id` IN (177, 178, 176)))
)

Čímž dělám jen 1 dotaz a ne 2. Dal jsem se tímto směrem protože:

  1. Explorer mi vytvoří jak ten Literal, tak ten konečný dotaz, tedy na SQL nemusím vůbec sáhnout a zachoval jsem práci s Explorerem, takže můžu nadále vesele používat buildery na skádání těch SQL dotazů a nejsem závislý na dané databázi
  2. Nemusel jsem tvořit jednoúčelový pohled
  3. To co vrátil ten dotaz na ten subselect bylo původně něco kolem 5000 řádků po přidání distinct se to snížilo na 500, ale pořád nechci tahat z databáze 500 idček, parsovat je a pak je jako řetězec v jiném SQL zase posílat zpátky, takže tím, že jsem místo vykonání toho dotazu si vytáhl pouze jeho podobu a použil jako literál jsem zachoval funkčnost Exploreru jako kdybych to dělal podle příkladu se 2 dotazy, ale zároveň jsem poslal jen jeden dotaz, který je řádově menší, než tahání IDček.
  4. Nezkoušel jsem, ale myslím, že by tento dotaz mohl být díky ušetřenému přenosu dat a místo joinu dělání subselectu rychlejší.

PS: Teoreticky by stačilo i něco jako:

SELECT COUNT(DISTINCT `catalog_id`)
FROM `catalog_x_category`
WHERE (`catalog_category_id` IN (177, 178, 176));

čímž bych dosáhnul stejného výsledku, jako u dotazu výše, ale vzhledem k tomu, že chci používat Selection metodu page, tak mi jiná cesta nezbývá

Editoval Polki (24. 11. 17:43)