Marek Olšavský: blog

Výběry z tabulek jsou často využívány v nejjednodušší možné podobě SELECT * FROM `tabulka` WHERE podmina, zkušenější programátoři hnězdičku moc nepoužívají, vyjmenují pouze sloupce které potřebují, čímž trochu sníží datová zátěž pro přenosy mezi serverem a aplikací. Hodně používaný je JOIN (celá řada „programátorů“ skončí u LEFT JOIN) a v oprávněných případech někdo používá podvýběry.

Podvýběry, sub SELECTy, jsou dobrý nástroj, ale ne vždy je triviální dostat data jak je potřebné lze se dostat do situace, kdy je nutné podvýběrem získat více sloupců a to MySQL nedovoluje. Za příklad poslouží aktuálně řešený případ – novinky pro jeden web, protože k novince může být přiřazený obrázek, ale nemusí, ne vždy je to pravidlem.

Jedna tabulka slouží pro uložení obsahu novinky:

CREATE TABLE `news` (
  `news_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `langs_id` int(10) unsigned NOT NULL DEFAULT '0',
  `socbook` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `visible` int(1) unsigned NOT NULL DEFAULT '0',
  `title` varchar(200) COLLATE utf8_czech_ci NOT NULL,
  `name` varchar(200) COLLATE utf8_czech_ci NOT NULL,
  `content` text COLLATE utf8_czech_ci NOT NULL,
  `add_date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `last_edit` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`news_id`),
  UNIQUE KEY `idx_name` (`name`,`langs_id`),
  KEY `idx_main_pg` (`langs_id`),
  KEY `add_date` (`add_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='persistent pages of web' AUTO_INCREMENT=14 ;

INSERT INTO `news` (`news_id`, `langs_id`, `socbook`, `visible`, `title`, `name`, `content`,
  `add_date`, `last_edit`) VALUES
(13, 1, 0, 1, 'Babyprofi.cz - Nový partner',
  'n13-babyprofi-cz-nov-partner', 'Oznamujeme zákazníkům, že naše zboží mohou zakoupit 
  u nového parntera firmy, kterým se stává firma xx.
',
  '2011-08-20 17:29:12', '2011-08-20 17:31:02'),
(12, 1, 0, 1, 'Babyprofi.cz - Nový partner', 'n12-babyprofi-cz-nov-partner',
  'Oznamujeme zákazníkům, že naše zboží mohou zakoupit u nového parntera firmy, 
  kterým se stává firma xx.
',
  '2011-08-20 17:28:58', '2011-08-20 17:28:58'),
(11, 1, 0, 1, 'Babyprofi.cz - Nový partner', 'n11-babyprofi-cz-nov-partner',
  'Oznamujeme zákazníkům, že naše zboží mohou zakoupit u nového parntera firmy, 
  kterým se stává firma xx.cz.
',
  '2011-08-20 17:27:29', '2011-08-20 19:09:48');

Druhá tabulka slouží pro přiřazení obrázku. Možná to vypadá komplikovaně, ale tento podivný systém mi šetří hodně starostí, protože obrázky, a bez problémů i další přílohy, nicméně teď mne zajímají opravdu jen obrázky, jsou uloženy na disku jen s id a koncovkou a v databázi je určeno kam patří, jaký mají typ. Tabulka pro soubory vypadá následovně:

CREATE TABLE `files` (
  `files_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL DEFAULT '0',
  `ptype` varchar(25) COLLATE utf8_czech_ci NOT NULL,
  `ftype` char(4) COLLATE utf8_czech_ci NOT NULL,
  `fname` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`files_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=219;

INSERT INTO `files` (`files_id`, `item_id`, `ptype`, `ftype`, `fname`) VALUES
(211, 41, 'products', 'jpg', 'bt359955.jpg'),
(217, 12, 'news', 'jpg', 'logo.jpg'),
(218, 13, 'news', 'jpg', 'abc_design_logo.jpg');

V tabulkách jsem ponechal i nějaká data, aby šlo testovat.

Při výběru potřebuji 5 posledních záznamů, jež jsou označeny jako zobrazitelné, tj. ve sloupci `visible` nemají nulu, seřadit je sestupně podle data a pokud existuje k nim obrázek, tak jej hned vybrat také. Programátor, který nepřemýšlí by vybral potřebné novinky, pak pole s novinkami cyklem prošel a doplnil si obrázky do pole. Neboli, pokud bude chtít zobrazovat posledních 5 novinek, znamená to 6 dotazů na databázi, což není mnoho, ale pokud jsou stránky dostatečně hodně navštěvované, je dobré odstranit každý nadbytečný dotaz. Je to cesta, do níž opravdu nechci, byť funguje a ve starších zdrojových kódech jsem tento postup viděl.

Další metoda, udělat podvýběr, nefunguje. Minimálně ne v MySQL, jež dovoluje vybrat v subSELECTu jediný sloupec. Takže takto ne:

SELECT
  `t1`.`news_id`,
  t1`.`title`,
  `t1`.`content`,
  `t1`.`add_date`,
  (SELECT
    `t2`.`files_id`,
    `t2`.`ftype`,
    `t2`.`fname`
  FROM
    `files`
  WHERE
    `ptype`='news' AND `t1`.`news_id`=`t2`.`item_id`
  ) FROM `news` AS `t1`
WHERE
  `t1`.`langs_id`='1' AND
  `t1`.`visible`<>'0'
ORDER BY
  `t1`.`add_date`
LIMIT 5

Spojením tabulek pomocí JOINu se dostávám správným směrem, ale také to nebude cesta zcela přímá, prosté propojení SELECT `t1`.`news_id`, `t1`.`title`, `t1`.`content`, `t1`.`add_date`, `t2`.`files_id`, `t2`.`ftype`, `t2`.`fname` FROM `news` AS `t1` LEFT JOIN `files` AS `t2` ON `t1`.`news_id`=`t2`.`item_id` WHERE `t1`.`langs_id`='1' AND `t1`.`visible`<>'0' AND `t2`.`ptype`='news' ORDER BY `t1`.`add_date` LIMIT 5 skrývá jedno úskalí. Vybral jsem sice novinky s obrázky, ale obrázek není povinná položka, takže nebude-li zadán, zahodil jsem novinku kvůli podmínce `t2`.`ptype`=’news‘, jíž vynechat nelze, protože stejná hodnota `item_id` se může objevit jak pro novinky, tak pro položky zboží, nebo partnery.

Ale JOIN je správná cesta, jak jsem našel v diskuzích, jen místo názvu tabulky připojuji výběr z tabulky files, který jsem patřičně omezil:

SELECT 
  `t1`.`news_id`, `t1`.`title`, `t1`.`content`, `t1`.`add_date`,
  `t2`.`files_id`, `t2`.`ftype`, `t2`.`fname` 
FROM `news` AS `t1` LEFT JOIN (
  SELECT DISTINCT `files_id`, `item_id`, `ftype`, `fname` FROM `files` WHERE `ptype`='news'
) AS `t2` ON `t1`.`news_id`=`t2`.`item_id` WHERE
  `t1`.`langs_id`='1' AND `t1`.`visible`<>'0' ORDER BY `t1`.`add_date` LIMIT 5

Efektivita nejspíše nebude moc slavná, ale rozhodně lepší, než při cyklickém tázání se na tabulku souborů. Určitě je potřeba si poladit indexy. V zápisku jsem postup vysvětlil na konkrétním příkladu, ale myslící programátor jej určitě dovede překlopit na své potřeby.

Aktualizace: Ještě výhodnější je použít CTE, Common Table Expressions, jež jsem vysvětloval na PostgreSQL, ale podobně fungují i v SQLite, nebo FirebirdSQL. MariaDB a MySQL již CTE také umí.