File indexing completed on 2024-04-21 06:00:37
0001 CREATE 0002 DEFINER = `root`@`localhost` PROCEDURE `fetchCatTreeForStore`( 0003 IN `STORE_ID` int(11) 0004 ) 0005 BEGIN 0006 0007 DROP TABLE IF EXISTS `tmp_store_cat`; 0008 0009 CREATE TEMPORARY TABLE `tmp_store_cat` 0010 ( 0011 INDEX `idx_cat_id` (`project_category_id`) 0012 ) 0013 ENGINE MEMORY 0014 AS 0015 SELECT `csc`.`store_id`, `csc`.`project_category_id`, `csc`.`order`, `pc`.`title`, `pc`.`lft`, `pc`.`rgt` 0016 FROM `config_store_category` AS `csc` 0017 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0018 WHERE `csc`.`store_id` = `STORE_ID` 0019 GROUP BY `csc`.`store_category_id` 0020 ORDER BY `csc`.`order`, `pc`.`title`; 0021 0022 SET @`NEW_ORDER` := 0; 0023 UPDATE `tmp_store_cat` SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0024 0025 0026 SELECT `sct`.`lft`, 0027 `sct`.`rgt`, 0028 `sct`.`project_category_id` AS `id`, 0029 `sct`.`title`, 0030 `scpc`.`count_product` AS `product_count`, 0031 `sct`.`xdg_type`, 0032 `sct`.`name_legacy`, 0033 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0034 (SELECT `project_category_id` 0035 FROM `stat_cat_tree` AS `sct2` 0036 WHERE `sct2`.`lft` < `sct`.`lft` 0037 AND `sct2`.`rgt` > `sct`.`rgt` 0038 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0039 LIMIT 1) AS `parent_id` 0040 FROM `tmp_store_cat` AS `cfc` 0041 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0042 JOIN `stat_store_prod_count` AS `scpc` 0043 ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`tag_id` IS NULL 0044 WHERE `cfc`.`store_id` = `STORE_ID` 0045 ORDER BY `cfc`.`order`, `sct`.`lft`; 0046 0047 END