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