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.

15.4.2009 03:33:02

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.

15.4.2009 05:15:55

Jakub Vrána :

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.

15.4.2009 05:27:30

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

15.4.2009 14:03:14

david@grudl.com:

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

15.4.2009 22:18:08

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

(reakce na) 16.4.2009 02:41:18

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.

16.4.2009 03:46:26

Jakub Vrána :

Ř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.

16.4.2009 05:04:47

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.

16.4.2009 16:52:29

Kajman:

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

16.4.2009 17:01:37

LesTR:

ach, sypu si tedy popel na hlavu.

16.4.2009 17:10:34

Kajman:

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

16.4.2009 06:48:08

david@grudl.com:

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

16.4.2009 10:50:59

Jakub Vrána :

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.

16.4.2009 10:57:23

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.

(reakce na) 16.4.2009 12:10:23

Jakub Vrána :

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.

16.4.2009 13:12:23

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ý?

16.4.2009 16:56:55

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í.

17.4.2009 04:29:24

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)

16.4.2009 16:07:14

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

17.4.2009 00:28:29

Jakub Vrána :

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.

17.4.2009 01:52:09

Karol:

Staci pridat

$vybrano[] = 0;

...jednoduche, ucinne :)

21.4.2009 14:42:45

v6ak:

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

21.4.2009 15:28:30

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

7.6.2009 07:52:12