Boj s Nette\Database: Subselect a výběr kolonek

Zdarec.

Při práci s Nette\Database jsem narazil na dvě neintuitivní věci, které mi zabraly nějaký čas než jsem je vyřešil. Tento příspěvek nechť slouží pro další generace (a mě, až na to zapomenu :-) ) jako návod, jak tyto situace řešit.

Používám aktuální vývojovou verzi Nette 2.1.

Mám databázi vozů (tabulka car) a dopravců (tabulka operator). Mezi vozy a dopravci je vazba M:N (přes tabulku car_operator), ve které je mimo jiné poznamenáno i pořadí dopravce u vozu (abych věděl, který dopravce měl vůz jako první, druhý atd.). Dump databáze viz konec příspěvku.

Na stránce s výpisem vozů chci filtrovat podle dopravce dejme tomu „ČSAD Brno-město“ (tzn. chci najít i vozy, které byly dříve vlastněny „ČSAD Brno-město“, ale dnes už jsou prodané jinému dopravci) a zároveň řadit podle posledního majitele vozu (tzn. nejdřív vozy, které jsou nyní vlastněné „ČSAD Brno-město“ a až pak vozy, které jsou vlastněné dopravcem abecedně až za ním, například „STUDENT AGENCY“).

Jak to udělat? Ze začátku jsem dost tápal. Zkoušel jsem dát subselect zjišťující aktuálního dopravce do funkce ->select() takto: table('car')->select('(SELECT ...) AS currentOperator') a následně pak v order volat ->order('currentOperator DESC'), ale nešlo to. NDB se snažila celý subselect rozparsovat. Obalil jsem to tedy do SqlLiteralu. Ani to nepomohlo (funkce select s ním vůbec nepočítá? Tohle je možný námět na zlepšení…). Ani syntaxe známá z funkce ->where('?', $argument) nefunguje, byť se otazník vůbec neošetří, a tak výsledná query vyžaduje dosazení argumentu, který tam ale není jak dostat.

// ukázka jednoho z pokusů
$cars = $this->db
	->table('car')
	->select(new SqlLiteral('
		IFNULL((
			SELECT name
			FROM operator AS o
			INNER JOIN car_operator AS co ON co.operator_id = o.id
			WHERE co.car_id = car.id
			ORDER BY co.order DESC
			LIMIT 1), \'\'
		) AS currrentOperator'))
	->where('car_operator:operator.name', 'ČSAD Brno-město')
	->order('currentOperator DESC');
// Skončí chybou `No reference found for $car->co`.
// NDB se snaží subselect rozparsovat, přestože se jedná o SqlLiteral

Jak jsem to nakonec vyřešil? Stačilo všechny identifikátory v subselectu obalit do znaků `. NDB se je tak nesnaží escapovat a vše projde. Je jedno, jestli se to celé obalí do SqlLiteralu, nebo ne.

Při další práci jsem narazil na podivnou věc. Kód:

// chci všechny vozy, seřazené podle dopravce
$cars = $this->db->table('car')
	->select('*')
	->order('car_operator:operator.name');

Položí do databáze dotaz:

SELECT *
FROM `car`
LEFT JOIN `car_operator` ON `car`.`id` = `car_operator`.`car_id`
LEFT JOIN `operator` ON `car_operator`.`operator_id` = `operator`.`id`
ORDER BY `operator`.`name`

Debug panel ukazuje 4 nalezené záznamy.

Ale když pak v kódu uděláte tohle:

foreach ($cars as $car) {
	echo $car->id;
}

Vypíší se jen dva záznamy.
Podle tohoto kódu:

echo $cars->count();

Byly nalezeny opravu jen dva záznamy.
Naopak tohle:

echo $cars->count('*');

Už vypíše číslo 4.

Jak je to možné?
Za vším hledej… kolonky v selectu :-) Tím, že jsme uvedli:

	->select('*')

…a joinujeme k další tabulce, se povedlo to, že ve výsledku, který nám vrátilo MySQL je dvakrát sloupec s názvem id. Jeden z tabulky car, druhý z tabulky operator, kterou joinujeme. Nette/Database používá při ukládání výsledku indexování v poli pomocí primárního klíče. Primární klíč je jen sloupec id a tak každý záznam uloží do pole $selection->data právě pod touto hodnotou. Jenomže vezme až tu druhou = tu, kterou nechceme = tu, z tabulky operators. Tím dojde k tomu, že se dva a dva záznamy navzájem přepíší a celkem je tedy máme jen dva.

Jak se to dá řešit? Stačí místo ->select('*') uvést ->select('car.*'). Pak se tahají opravdu jen sloupce z tabulky car a k žádné kolizi nedojde.

Tento problém by se možná dal označit za bug. Co myslíte?

Tohle by mohl být další impulz k zamyšlení o tomto. Je to vpodstatě stejný problém, akorát trochu z jiného konce.

Přeju vám ostatním, abyste se s takovými WTF Nette Database nikdy nesetkali. Na malý projekt je Nette\Database optimální. Čím větší a složitější struktura databáze, tím je ale použití problematičtější. A zdánlivé drobnosti jako už zmiňované toto mohou hrát velkou roli při odhalování chyb.

Na závěr ještě dump databáze, kdyby si to někdo chtěl vyzkoušet.

-- Adminer 3.6.0 MySQL dump

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `car`;
CREATE TABLE `car` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `numberPlate` varchar(12) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32199 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `car` (`id`, `numberPlate`) VALUES
(1,	'BSC 00-01'),
(2,	'BSC 00-02'),
(3,	'BSC 00-03'),
(4,	'BSC 00-04');

DROP TABLE IF EXISTS `car_operator`;
CREATE TABLE `car_operator` (
  `car_id` int(4) unsigned NOT NULL,
  `operator_id` int(11) NOT NULL,
  `order` int(4) NOT NULL,
  PRIMARY KEY (`car_id`,`operator_id`,`order`),
  KEY `car_id` (`car_id`),
  KEY `operator_id` (`operator_id`),
  CONSTRAINT `car_operator_ibfk_1` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`),
  CONSTRAINT `car_operator_ibfk_2` FOREIGN KEY (`operator_id`) REFERENCES `operator` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `car_operator` (`car_id`, `operator_id`, `order`) VALUES
(1,	1,	1),
(2,	1,	1),
(3,	2,	1),
(4,	2,	1);

DROP TABLE IF EXISTS `operator`;
CREATE TABLE `operator` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `operator` (`id`, `name`) VALUES
(1,	'ČSAD Brno-město'),
(2,	'STUDENT AGENCY');

-- 2013-03-21 18:17:16