· 6 years ago · Jan 20, 2020, 09:16 PM
1CREATE PROCEDURE `CategoryLoadAllPaged`(
2 `ShowHidden` bool,
3 `Name` text,
4 `StoreId` int,
5 `CustomerRoleIds` text,
6 `PageIndex` int,
7 `PageSize` int,
8 OUT `TotalRecords` int
9)
10sql security invoker
11BEGIN
12 Set @lengthId = (select CHAR_LENGTH(MAX(Id)) FROM Category);
13 Set @lengthOrder = (select CHAR_LENGTH(MAX(DisplayOrder)) FROM Category);
14 drop temporary table if exists OrderedCategories;
15 create temporary table `OrderedCategories` (
16 `id` int,
17 `Order` text
18 );
19
20 insert into `OrderedCategories`
21 with recursive CategoryTree AS
22 (
23 SELECT id, cast(concat(LPAD(DisplayOrder, @lengthOrder, '0'), '-' , LPAD(Id, @lengthId, '0')) as char(500)) as `Order`
24 FROM category
25 WHERE ParentCategoryId = 0
26 UNION ALL
27 SELECT c.id, concat(sc.`Order`, '|', LPAD(c.DisplayOrder, @lengthOrder, '0'), '-' , LPAD(c.Id, @lengthId, '0')) as `Order`
28 FROM CategoryTree AS sc
29 JOIN category AS c ON sc.id = c.ParentCategoryId
30 )
31 select *
32 from CategoryTree;
33
34 select c.`Id`, c.`Name`, ct.`Order`
35 from category c
36 inner join `OrderedCategories` as ct on c.Id = ct.Id
37 #filter results
38 where not c.Deleted
39 and (ShowHidden OR c.Published)
40 and (COALESCE(`Name`, '') = '' OR c.`Name` LIKE concat('%', `Name`, '%'))
41 and (ShowHidden OR COALESCE(`CustomerRoleIds`, '') = '' OR not c.SubjectToAcl
42 OR EXISTS (
43 select 1
44 from aclRecord as acl
45 where find_in_set(acl.CustomerRoleId, CustomerRoleIds)
46 and acl.`EntityId` = c.`Id` AND acl.`EntityName` = 'Category')
47 )
48 and (not StoreId OR not c.`LimitedToStores`
49 OR EXISTS (SELECT 1 FROM storemapping sm
50 WHERE sm.`EntityId` = c.`Id` AND sm.`EntityName` = 'Category' AND sm.`StoreId` = StoreId
51 )
52 )
53 and ct.Id > `PageSize` * `PageIndex`
54 order by ct.`Order`, 1
55 LIMIT `PageSize`;
56
57 select count(*) from `OrderedCategories` into `TotalRecords`;
58
59 drop temporary table if exists OrderedCategories;
60END$$