Špatné zpracování sql dotazu do databáze

Dominik

Zdravím,

momentálně řeším problém, že mi databáze vrací jiné výsledky na základě toho, zda použiji funkci z Nette Database query se 2 parametry nebo jen s 1 parametrem.

Přesněji tedy pokud napíši něco jako

return $this->db->query('SELECT * FROM `product_view` WHERE (
	(
 property_id = 9
 AND `option` >= 621
 )
 OR (
 property_id = 34
 AND `option` >= 621
 AND `product` IN (1322, 1323,1324)
 )
	)
GROUP BY `product`');

tak je výsledkem ±800 řádku.

Pokud ale použiji kód, kde oddělím data od zbytku query (tak jak to potřebuji kvůli přoměnným datům),

(toto je jedna z více iterací, kde jsem se snažil správně dostat data a sql dohromady, ale neúspěšně. Zkoušel jsem i notaci „čistého“ sql pouze s otazníky, kde jsem pak níže doplňoval hodnoty, ale také neúspěšně)

return $this->db->query('SELECT * FROM ?name WHERE', [
				self::PRODUCT_VIEW,
				$this->db::literal('?or', [
					$this->db::literal('?and', [
						'property_id' => $propertyFallback,
						'option >= ?' => $value,
					]),
					$this->db::literal('?and', [
						'property_id' => $propertyMain,
						'option >= ?' => $value,
						'product' => $subQuery,
					])
				])
			]);

Tak mi to vrátí bohužel pouze ±400 rádků.

V tracy se oba sql dotazy zobrazují naprosto stejně (pouze s rozdílným počtem vrácených řádků).

V obou případech jsem na stejné databázi a mám vypnutou a promazanou cache.

Zkoušel jsem i přístup pres Nette Explorer, ale výsledek byl stejný jako u Nette Database query s 2 a více argumenty.

Nevím, zda mám nějakou chybku v sql dotazu, který by následně tento problém způsoboval, ale spíše mi příjde, že tam preprocessor něco pochopí špatně.

Předem moc děkuji za jakoukoliv pomoc.

Editoval Dominik (19. 2. 18:42)

Pepino

@Dominik U jednoho máš GROUP BY a u druhého ne.

Dominik

@Pepino Pardon zrovna špatná kombinace, ale problém tam je pořád. Zde už kompletně stejné dotazy (jeden napsaný napřímo a 2. s args).

Toto vrací ± 1000 řádků momentálně (přes noc přibyly data)

SELECT DISTINCT product AS id
FROM product_view
WHERE (((`property_id` = 9) AND (`option` >= 582)) OR ((`property_id` = 34) AND (`option` >= 582) AND
(`product` IN (1322, 1323, 1324, 1325, 1326, 1327, 1328, 1329, 1330, 1332, 1333, 1334, 1336, 2065, 2066, 2067, 2068, 2149, 2151, 2153, 2154))))

Toto vrací ±700 řádků (mám zkontrolovaných pár id produktů, u kterých vím, že tam musí být a v těchto ±700 řádcích nejsou)

return $this->db->query('SELECT DISTINCT product AS id FROM ?name WHERE', self::PRODUCT_VIEW, [
				$this->db::literal('?or', [
					$this->db::literal('?and', [
						'property_id' => $propertyFallback,
						'option >= ?' => $value,
					]),
					$this->db::literal('?and', [
						'property_id' => $propertyMain,
						'option >= ?' => $value,
						'product' => $subQuery,
					])
				])
			]);

Kamil Valenta

Je v tom hodně pro nás neznámých, třeba jak vypadá onen subdotaz, který vrací id produktů.
Zkus si před tím zavolat

SET optimizer_switch='subquery_cache=off';

protože subdotazy mohou být za určitých okolností cachované.

Dominik

@KamilValenta přidání jsem zkusil, bohužel bez úspěchu.

subdotaz tam úplně nemám pouze výše si z jiné tabulky vytáhnu id produktů podle určitých podmínek a z toho udělám pole, které předávám té proměnné $subQuery.

$subQuery = $this->db->table(self::PRODUCT_PROPERTY)
			->select('DISTINCT product AS id')
			->where('property', $propertyMain)
			->where('lang', $lang)
			->group('product')
			->fetchPairs(null, 'id');

to mi vrátí ten výsledek [1322, 1323, 1324, 1325, 1326, 1327, 1328, 1329, 1330, 1332, 1333, 1334, 1336, 2065, 2066, 2067, 2068, 2149, 2151, 2153, 2154]

zde je přehlednější verze toho query, které vrací méně výsledků

$this->db->query('SELECT DISTINCT product AS id FROM ?name
WHERE (
	(
		(property_id = ?)
		AND (`option` >= ?)
	)
	OR (
		(property_id = ?)
		AND (`option` >= ?)
		AND (product IN (?))
	)
)', self::PRODUCT_VIEW,
				$propertyFallback, // 9
				$value, // 621
				$propertyMain, // 34
				$value, // 621
				$subQuery // [1322, 1323, 1324, 1325, 1326, 1327, 1328, 1329, 1330, 1332, 1333, 1334, 1336, 2065, 2066, 2067, 2068, 2149, 2151, 2153, 2154]
);

Ta query by každopádně měla vrátit data, která buď mají value větší než 621 s property_id 9 nebo mají value větší než 621 a mají property_id 34 a mají id produktu něco z $subQuery.

Ještě teda doplním, že tabulka product_view je pohled a ne plnohodnotná tabulka a má následující strukturu:

+-------------+------------------+-----------+
| Sloupec     | Typ              | Komentář  |
+-------------+------------------+-----------+
| product     | int(10) unsigned |           |
| property    | varchar(255)     | NULL      |
| property_id | int(10) unsigned | NULL      |
| option      | varchar(255)     | NULL      |
| option_id   | int(10) unsigned | NULL      |
| lang        | int(4)           | NULL      |
+-------------+------------------+-----------+

Marek Bartoš

Jsi si naprosto jistý, že se ti v Tracy baru zobrazují pro oba případy totožné dotazy?
Nejsou na konci sql tečky? Může to být zobrazené oříznuté – ovlivníš přes \Tracy\Debugger::$maxLength = 999999;
V databázi si též můžeš zapnout general log a kouknout se na logy dotazů tam.

Editoval Marek Bartoš (20. 2. 13:37)

Kamil Valenta

U dotazu s ± 700 řádky dáváš option >= 621.
U dotazu s ± 1000 řádky dáváš option >= 582.

Dominik

@MarekBartoš Query parametry si dumpuju přes tracy log dotazů, tam opravdu dle mě rozdíl není. Ale nevím, jestli to nette nestaví pro tracy nějak už samo. Zde je obrázek, jak ten výpis vypadá (pořadí těch query pod sebou odpovídají tomu, co je v tom okně napravo): https://imgur.com/a/P5WC9Hb

Zkoušel jsem si i dumpnout jakoby sql, co vrací fce getQueryString, ale tam když používám parametry, tak to vrací jen ? místo hodnot. Ale strukturou to stále odpovídá tomu, co to má vracet:

SELECT DISTINCT product AS id FROM `product_view`
WHERE (
    (
        (property_id = ?)
        AND (`option` >= ?)
    )
    OR (
        (property_id = ?)
        AND (`option` >= ?)
        AND (product IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
    )
)

V tomto případě přes tracy uplně neloguju, kromě těch query. Data si kontroluju přímo pres Xdebug, kde vidím celé výsledky.

Dominik

Kamil Valenta napsal(a):

U dotazu s ± 700 řádky dáváš option >= 621.
U dotazu s ± 1000 řádky dáváš option >= 582.

Pardon toto je moje chyba. V příkladu, na který odkazuješ jsem měl nastavenou zrovna jinou hodnotu a proto byly výsledky řádků jiné, ale bohužel stejně špatné a rozdílné

správné vysledky by měly být:
pro hodnotu 621: ±800 řádků
pro hodnotu 582: ±1000 řádků

Kamil Valenta

Do where Ti vstupuje „option“ (varchar), ale porovnáváš ho jako int. Víš určitě, že to tak chceš? Nemá tam být „option_id“ >= 621?
Porovnávat varchar s číslem sice lze, ale výsledek může být neočekávaný a ještě více legrace je, když do hodnoty vstoupí nějaký white-znak, který třeba ani v Tracy okem nevidíš.

Zkus si ve where zakomentovat podmínky na „option“, jestli se počty nesrovnají. Můžeš tak zkusit postupně odpojovat každý sloupec, ale ten varchar je adept žhavý…

Dominik

@KamilValenta Děkuju moc za pomoc.

Opravdu to bylo tím porovnáváním varchar vs int. Dal jsem tam cast a už to jede správně. Bohužel vzhledem ke strukturře db potřebuji porovnávat varchar s číslem.

Děkuju i ostatním za snahu pomoct.