Operátor IN s prázdnou množinou

Školení, která pořádám

V MySQL, stejně jako v několika dalších databázích, lze používat operátor IN testující příslušnost do množiny. Pokud je tato množina uložena v PHP poli, tak se operátor dobře se používá spolu s funkcí implode.

Řekněme, že v poli máme uložený výběr nějakých článků a chceme vypsat seznam skupin spolu s počtem vybraných článků v jednotlivých skupinách. K tomu se dá použít následující dotaz:

<?php
$result = mysql_query("
    SELECT skupiny.*, COUNT(clanky.id) AS pocet_clanku
    FROM skupiny
    LEFT JOIN clanky ON skupiny.id = clanky.skupina AND clanky.id IN (" . implode(", ", $vybrano) . ")
    GROUP BY skupiny.id
");
?>

Problém nastane, pokud bude pole $vybrano prázdné, protože IN () způsobí poněkud nešťastně syntaktickou chybu. Odstranit z dotazu celou tabulku clanky by v podstatě znamenalo napsat dotaz znovu, což by vedlo k jeho pracnějším pozdějším úpravám. Dá se proto použít trik, který dotaz zachová a přesto z tabulky clanky nic nevybere:

<?php
$result = mysql_query("
    SELECT skupiny.*, COUNT(clanky.id) AS pocet_clanku
    FROM skupiny
    LEFT JOIN clanky ON skupiny.id = clanky.skupina AND clanky.id IN (" . ($vybrano ? implode(", ", $vybrano) : "NULL") . ")
    GROUP BY skupiny.id
");
?>

Hodnota NULL není rovna žádné jiné hodnotě (ani hodnotě NULL), takže dotaz z tabulky clanky nic nevybere. Dotaz bude nejspíš o něco neefektivnější, než kdybychom z něj tabulku clanky úplně odfiltrovali, ale zase bude jednodušší jeho údržba. V PHP 5.3 lze použít zkrácený ternární operátor (implode(", ", $vybrano) ?: "NULL").

dibi patch pending.

Jakub Vrána, Dobře míněné rady, 15.4.2009, diskuse: 28 (nové: 0)

Diskuse

Ondra:

Hodnotu "NULL" lze do testované množiny také paušálně přidat vždy. Sice to bude leckdy zbytečná práce navíc, ale mám bez testování zaručeno, že dotaz nikdy neselže.

Juraj Krivda:

Nie je efektívnejšie pred samotným dotazom otestovať či je pole $vybrano naplnené a ak áno tak vykonať dotaz? Ušetrí sa jeden zbytočný SQL select.

ikona Jakub Vrána OpenID:

Schválně jsem ten dotaz sestavil tak, aby bylo vidět, že ho musím vykonat vždy – chci seznam skupin (vždy) s počtem vybraných článků (pokud jsou). Takže by se dal ušetřit jenom JOIN, což by zásadní přínos nemělo, a dotaz by se tím zkomplikoval.

ikona david@grudl.com:

Ty víš jak se připomenout :-))

Tyhle vychytávky pro krajní situace se mi líbí, např. WHERE [true] a teoreticky funkční ORDER BY 1, i tohle je dobrý nápad. Jenže zatímco pro %l bych to přidal hned, v druhém případě (větev default) jsem váhám. No a pak jsem jel na pár dní pryč a zapoměl... ;)

ikona david@grudl.com:

Patch dopendován http://jdem.cz/bbcd3 (a vypnul jsem tam na tvou radu i magic_quotes_runtime)

TenTřetí:

Trošku off topic, ale je rozdíl v náročnosti na db, pokud v SQL použiju WHERE [true] nebo WHERE 1=1 ? Tedy zda se 1=1 musi pokazde znovu a znovu vyhodnocovat. Díky

LesTR:

Mě osobně se toto řešení nelibí a to z důvodu, že je závislé na benevolentním modu mysql. Pokud budu mít nastavený např. ANSI mod, tak korektně umře i tohle řešení.
Když už dle podmínky něco do SQL přilepovat, tak celé i s IN.

Nevím, jestli nějak dibi řeší "abstrakci" nad SQL, potažmo db enginy. Pokud ano, tak by takovouto věc imho nemělo(la? :D) obsahovat.

ikona Jakub Vrána OpenID:

Řešení v ANSI módu neumře (ověřeno), navíc nechápu, proč by mělo umřít „korektně“.

Ternární operátor by samozřejmě mohl obsahovat celou podmínku, to by se ale nedalo řešit automaticky právě třeba v dibi.

LesTR:

To "korektně" měl být k tomu, že se člověk snaží porovnávat hrušky s jablkama, proto má umřít.
To že při ANSI modu umře jsem samozřejmě testoval. Viz.

mysql> select * from test where id in ("NULL");
Empty set (0.00 sec)

mysql> set sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id in ("NULL");
ERROR 1054 (42S22): Unknown column 'NULL' in 'where clause'

Možná na to má vliv nějaká další konfigurační volba, zkoumat to teď dál nebudu.
Jako rozhodně jsem to řešení nechtěl nijak hanit, chtěl jsem spíše ukázat na podmínku, která se může špatně hledat.

Kajman:

Ale null není název sloupečku, tak ho nedávejte do uvozovek.

LesTR:

ach, sypu si tedy popel na hlavu.

Kajman:

Jen pozor, že pro "not in" se null nedá použít stejně :-)

ikona david@grudl.com:

Máš recht... To by se ale mělo pořešit. Jakube?

ikona Jakub Vrána OpenID:

Napadá mě leda dovolit určit hodnotu, která se použije pro prázdné pole. Ve většině případů to asi bude 0, ale jako výchozí je lepší NULL i za cenu špatného chování s NOT IN.

ikona v6ak:

Nejsem sice Jakub, ale:
1. V DibiFluent snad není problém, ne? Tady je možné přeložit celou podmínku na true nebo false.
2. Jinde mě nenapadá nic lepšího, než taky zapouzdřit celý IN výraz.

ikona Jakub Vrána OpenID:

Problém je ten, že když napíšu "id NOT IN (", $array, ")", tak to s prázdným polem v minulosti způsobilo syntaktickou chybu, teď to bude fungovat špatně (nevybere to nic, i když by to logicky mělo vybrat všechno).

Samozřejmě se o to lze postarat na vyšší úrovni (to šlo i dřív), ale já bych to chtěl automaticky přímo v dibi.

Kajman:

Může se tam dát např. poddotaz, co nevrátí žádný řádek. Ale v mysql se tuším zatím provádí poddotazy v in() stále pro každý řádek, tak by to celkem zdržovalo. Jestli tam nedát nějaký šílený řetězec - nesystémové, ale mělo by to být rychlejší.

A úsměvné je, že mysql 5.0.51 nevrací pro tyhle trojice stejný výsledek, i když by měla (oracle nemá problém:)

select 1 from dual where 1 not in (select 1 from dual where 1=2);
select 1 from dual where 1 not in (select 2 from dual where 1=2);
select 1 from dual where 1 not in (select null from dual where 1=2);

select 1 from dual where 1 in (select 1 from dual where 1=2);
select 1 from dual where 1 in (select 2 from dual where 1=2);
select 1 from dual where 1 in (select null from dual where 1=2);

Nemáte někdo nainstalovanou poslední verzi mysql, jestli je ten bug už opravený?

Kajman:

Tak i v 5.1.33 mají stále tu chybu, ale jde to takhle:
not in (select null from (select 1 from dual) t where false)

Jen to je může být zpomalení.

ikona v6ak:

A proč by to nemohlo být přímo v Dibi?
1. V DibiFluent to bude asi celkem přímočaré
2. V DibiConnection::query to sice může být trošku násilné, ale nic lepšího zatím nemáme.
Btw zkoušeli jste někdo dát místo množiny něco jiného (null, číslo, string, ...)? Já teď nemám moc po ruce mysql. (Dobře, kdysi jsem napsal komfortní BrainFuck IDE na hloupém mobilu pod 2000Kč s rozlišením 160*128px s použitím html a js, ale byl to zhruba stejný úlet jako celý BrainFuck :-D)

Martin:

Přijde mi to zbytečně krkolomné. Už v PHP kódu ověřuji, zda nějaké hodnoty množiny IN existují a pokud ano, vložím pomocí proměnné do MySQL část dotazu s IN. Pokud neexistují, proměnná bude prázdná a do dotazu se nic nevloží.

Jinak taky často používám pro ověření krajní pravdivosti konstrukci WHERE 1=1

ikona Jakub Vrána OpenID:

Tady by se hodila negativní podmínka, tedy např. 0 nebo 1=0. Samozřejmě to udělat jde, jen to bude o něco míň přehlednější, ale především se to nedá přesunout do nezávislé vrstvy - např. dibi.

Karol:

Staci pridat

$vybrano[] = 0;

...jednoduche, ucinne :)

ikona v6ak:

Jednoduchost tomu neupíram. Ale asi to nebude zrovna univerzální řešení, takže s tou účinnosti to je jak kdy.

Mike:

Mám související dotaz. Lze nějak zařídit, aby byl výsledek seřazen podle pořadí hodnot v seznamu?

Tj. "SELECT Jmeno FROM Data WHERE ID IN (3, 5, 1, 2)"

Mi vždy vyhodí výsledek seřazený dle ID 1,2,3,5, ale já potřebuji aby byl seřazený podle pořadí v seznamu , tj. 3,5,1,2.

Díky za každý podnět

ikona Jakub Vrána OpenID:

http://php.vrana.cz/setrideni-zaznamu-podle…-kriteria.php

Mike:

To je úžasné, přesně tohle jsem potřeboval. Co jsem se nahledal...
Moc děkuji.

Francek Vosmrádlo:

a co udělat podmínku takto?
... AND clanky.id IN (NULL, " . implode(", ", $vybrano) . ")

ikona Jakub Vrána OpenID:

Je to dobrý nápad. Ale raději bych zkontroloval, jestli se v takovém případě správně použijí indexy.

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.