File indexing completed on 2024-04-14 05:53:06
0001 DROP PROCEDURE IF EXISTS `fetchCatTreeWithTagsForStore`; 0002 0003 DELIMITER $$ 0004 0005 CREATE 0006 DEFINER = CURRENT_USER PROCEDURE `fetchCatTreeWithTagsForStore`(IN `STORE_ID` INT(11), 0007 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`, 0017 `csc`.`project_category_id`, 0018 `csc`.`order`, 0019 `pc`.`title`, 0020 `pc`.`lft`, 0021 `pc`.`rgt` 0022 FROM `config_store_category` AS `csc` 0023 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `csc`.`project_category_id` 0024 WHERE `csc`.`store_id` = `STORE_ID` 0025 GROUP BY `csc`.`store_category_id` 0026 ORDER BY `csc`.`order`, `pc`.`title`; 0027 0028 SET @`NEW_ORDER` := 0; 0029 0030 UPDATE `tmp_store_cat_tags` 0031 SET `order` = (@`NEW_ORDER` := @`NEW_ORDER` + 10); 0032 0033 SELECT `sct`.`lft`, 0034 `sct`.`rgt`, 0035 `sct`.`project_category_id` AS `id`, 0036 `sct`.`title`, 0037 `scpc`.`count_product` AS `product_count`, 0038 `sct`.`xdg_type`, 0039 `sct`.`name_legacy`, 0040 if(`sct`.`rgt` - `sct`.`lft` = 1, 0, 1) AS `has_children`, 0041 (SELECT `project_category_id` 0042 FROM `stat_cat_tree` AS `sct2` 0043 WHERE `sct2`.`lft` < `sct`.`lft` 0044 AND `sct2`.`rgt` > `sct`.`rgt` 0045 ORDER BY `sct2`.`rgt` - `sct`.`rgt` 0046 LIMIT 1) AS `parent_id` 0047 FROM `tmp_store_cat_tags` AS `cfc` 0048 JOIN `stat_cat_tree` AS `sct` ON find_in_set(`cfc`.`project_category_id`, `sct`.`ancestor_id_path`) 0049 JOIN `stat_store_prod_count` AS `scpc` 0050 ON `sct`.`project_category_id` = `scpc`.`project_category_id` AND `scpc`.`tag_id` = `TAGS` 0051 WHERE `cfc`.`store_id` = `STORE_ID` 0052 ORDER BY `cfc`.`order`, `sct`.`lft`; 0053 END$$ 0054 0055 DELIMITER ;