VbzCart/queries/qryCat pages
Jump to navigation
Jump to search
About
- Purpose: Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types
- Requires: cat_supp,
- REDIRECT Template:l/vc/query, qryCat_Titles, cat_images
- History:
- 2009-12-10 Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names
- 2010-11-08 Renaming ID to ID_Row so this query can be used as a drop-in for cat_pages
SQL
<mysql>CREATE OR REPLACE VIEW `qryCat_pages` AS
SELECT concat_ws('-','S',s.ID) AS AB, lcase(s.CatKey) AS Path, s.ID AS ID_Row, 'S' AS Type FROM cat_supp AS s UNION SELECT concat_ws('-','D',d.ID) AS AB, lcase(d.CatWeb_Dept) AS Path, d.ID AS ID_Row, 'D' AS Type FROM qryCat_Depts AS d UNION SELECT concat_ws('-','T',t.ID) AS AB, replace(lcase(t.CatWeb),'-','/') AS Path, t.ID AS ID_Row, 'T' AS Type FROM qryCat_Titles AS t UNION SELECT concat_ws('-','I',i.ID) AS AB, lcase(concat_ws('/',replace(t.CatWeb,'-','/'),i.AttrFldr,i.Ab_Size)) AS Path, i.ID AS ID_Row, 'I' AS Type FROM (cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title = t.ID) WHERE (i.Ab_Size NOT IN ('th','sm'));</mysql>
Archive
This is how the query was actually defined (which didn't work anymore, by 2009-12-10): <mysql>CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `VbzCart`.`_cat_pages` AS select
concat_ws(_utf8'-',_utf8'S',`VbzCart`.`suppliers`.`ID`) AS `AB`, lcase(`VbzCart`.`suppliers`.`CatKey`) AS `Path`, `VbzCart`.`suppliers`.`ID` AS `ID`, _utf8'S' AS `Type`
from `suppliers` union select
concat_ws(_utf8'-',_utf8'D',`VbzCart`.`_depts`.`ID`) AS `AB`, lcase(`VbzCart`.`_depts`.`CatWeb_Dept`) AS `Path`, `VbzCart`.`_depts`.`ID` AS `ID`, _utf8'D' AS `Type`
from `_depts`
where `VbzCart`.`_depts`.`cntForSale`
union select
concat_ws(_utf8'-',_utf8'T',`VbzCart`.`_titles`.`ID`) AS `AB`, replace(lcase(`VbzCart`.`_titles`.`CatWeb`),_latin1'-',_latin1'/') AS `Path`, `VbzCart`.`_titles`.`ID` AS `ID`,_utf8'T' AS `Type`
from `_titles` union select
concat_ws(_utf8'-',_utf8'I',`i`.`ID`) AS `AB`, lcase(concat_ws(_latin1'/',replace(`t`.`CatWeb`,_latin1'-',_latin1'/'),`i`.`AttrFldr`,`i`.`Ab_Size`)) AS `Path`, `i`.`ID` AS `ID`, _utf8'I' AS `Type`
from (`cat_images` `i` left join `_titles` `t` on((`i`.`ID_Title` = `t`.`ID`))) where (`i`.`Ab_Size` not in (_latin1'th',_latin1'sm'))</mysql>