Kolize při spojování příkazů, které vrací NULL

bumprask

Používám Nette 2.0 a PHP 5.3 a objevil jsem cosi co mi dělá starosti. V případě, kdy chci vytvořit výběr z nějaké tabulky a daný výběr neodpovídá podmínce, database vrátí na tento dotaz hodnotu NULL. Ovšem v případě, že chci v jiném dotazu použít předchozího výsledku za klauzulí IN database spojí dané výsledky tak, že v SQL to vypadá takto SELECT * FROM contact WHERE id IN(NULL)…což mysql vyhodnotí špatně a nevrátí nic, jelikož vyžaduje tvar WHERE id IN(‚NULL‘).

Setkal se někdo s tímto problémem, případně existuje jiný způsob jak docílit zkombinování dvou výsledků pomocí IN, aby v případě prázdného výsledku dosazované proměnné databáze vrátila všechny záznamy? Možná jsem jen špatně pochopil logiku skládání příkazů, prosím o radu. Díky.

$accessContact = $this->researchModel->getAccessBlocks()
                            ->select("contact_id")
                            ->where("UNIX_TIMESTAMP() - block_time <=?",$this->blockTime)

	$contact = $this->researchModel->getContacts()->where("contact.id NOT",$accessContact);

	//toto je vysledny format SQL dotazu, kde MySQL vrati 0 zaznamu z tabulky contact
	SELECT `id` FROM `contact` WHERE (`contact`.`id` NOT IN (NULL))

LeonardoCA

Spíše bych se zamyslel jak docílit výsledku bez použití IN(), třeba pomocí JOIN, záleží čeho chceš dosáhnout.

Jinak za předpokladu, že neexistuje ‚id‘ s hodnotou „0“ melo by projit

SELECT `id` FROM `contact` WHERE (`contact`.`id` NOT IN IFNULL(NULL,0))

Editoval LeonardoCA (30. 5. 2012 15:45)

Ascaria

Nejlepší by byl join, ale zkus třeba:

$accessContact = Array(-1) + (array)$this->researchModel->getAccessBlocks()
                            ->select("contact_id")
                            ->where("UNIX_TIMESTAMP() - block_time <=?",$this->blockTime)

$contact = $this->researchModel->getContacts()->where("contact.id NOT", $accessContact);

ta –1 je tam proto, aby pole $accessContact nezůstalo prázdné, když to nic nevrátí – dotaz by pak měl NOT IN() a předpokládám že máš IDčka jen kladné, tak –1 navíc nevadí, mohla by být i nula, ale tu nevím jestli používáš.

duke

Myslím, že jde o chybu v Nette\Database u této metody, kde se v případě použití IN s poddotazem (pouze však v případě MySQL) kvůli optimalizaci poddotaz provede předem a pokud nevrátí žádný výsledek je použito NULL. Toto řešení stačí v případě IN, ale nikoli už v případě NOT IN. V případě NOT IN (s prázdnou množinou) je třeba, aby se taková podmínka vůbec do sql dotazu nepřidala. Alternativně by šlo místo NULL použít nějakou vyhrazenou konstantu, která by byla dostatečně unikátní, ale to už není tak čisté řešení.

Tento bug se netýká jen poddotazů, ale také případu:

$books = $db->table('book')->where('NOT id', array()); // nevrátí nic, ač by mělo vrátit všechny řádky

Editoval duke (21. 11. 2012 12:22)

LeonardoCA

Ascaria:
to už je lepší to moje nepěkné řešení :-)

bumprask

LeonardoCA: „Spíše bych se zamyslel jak docílit výsledku bez použití IN(), třeba pomocí JOIN, záleží čeho chceš dosáhnout.“

Můžete mi prosím někdo nastínit jak by daný dotaz vypadal s použitím JOIN, aby výsledek byl zpracovatelný jako typ Table Selection (prostě objekt nette database), pač join dle dokumentace lze použít jenom u konstrukce přímého zápisu SQL dotazu ve funkci ->query(„dotaz JOIN dotaz“) nebo ->exec(„dotaz JOIN dotaz“), které tuším nevrací Table Selection, ale přímo výsledek v poli, nebo se pletu?

Ascaria

Neumím s nette\database, ale co třeba něco jako:

$contact = $this->researchModel->getContacts();

$accessContact = $this->researchModel->getAccessBlocks()
                            ->select("contact_id")
                            ->where("UNIX_TIMESTAMP() - block_time <=?",$this->blockTime);

if(null !== $accessContact)
    {
    $contact = $contact->where("contact.id NOT",$accessContact);
    }

duke

@Ascaria $accessContact neobsahuje výsledek dotazu ale objekt představující dotaz, který rozhodně není ekvivalentní NULL, takže spíše nějak takto:

$contacts = $this->researchModel->getContacts();

$accessContacts = iterator_to_array(
	$this->researchModel->getAccessBlocks()
		->select("contact_id")
		->where("UNIX_TIMESTAMP() - block_time <= ?", $this->blockTime)
);

if (!empty($accessContacts))
{
	$contacts = $contacts->where("NOT contact.id", $accessContacts);
}

Lepší řešení ale bude oprava Nette\Database. V případě použití jiné databáze než MySQL toto řešení zbytečně zvyšuje režii pro PHP, neboť neumožňuje použít vnořený dotaz přímo v SQL (MySQL se toto netýká, protože v MySQL se vnořené dotazy u konstrukce IN z optimalizačních důvodů nepoužívají).