`Category1ID` int(11) NOT NULL AUTO_INCREMENT,

`Category1Name` varchar(45) DEFAULT NULL,

`Category1Name_FR` varchar(45) DEFAULT NULL,

`Category1Photo` varchar(45) DEFAULT NULL,

`Category1Order` int(3) DEFAULT NULL,

`Category1Status` int(1) DEFAULT 1,

PRIMARY KEY (`Category1ID`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Top level category';

CREATE TABLE `categories_2` (

`Category2ID` int(11) NOT NULL AUTO_INCREMENT,

`Category2Name` varchar(45) DEFAULT NULL,

`Category2Name_FR` varchar(45) DEFAULT NULL,

`Category2Order` int(3) DEFAULT NULL,

`Category2Status` int(1) DEFAULT 1,

`Category1ID` int(11) DEFAULT NULL COMMENT 'To which parent level category it fits',

PRIMARY KEY (`Category2ID`)

) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='Mid level category';

CREATE TABLE `categories_3` (

`Category3ID` int(11) NOT NULL AUTO_INCREMENT,

`Category3Name` varchar(45) DEFAULT NULL,

`Category3Name_FR` varchar(45) DEFAULT NULL,

`Category3Order` int(3) DEFAULT NULL,

`Category3Status` int(1) DEFAULT 1,

`Category2ID` int(11) DEFAULT NULL COMMENT 'To which parent level category it fits',

PRIMARY KEY (`Category3ID`)

) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='Bottom level category';

INSERT INTO `categories_1` VALUES (1,'Meat','Meat-fr','meat.jpg',1,1),(2,'Fish & Sea Food',NULL,'fish.jpg',2,1),(3,'Fruit & Vegetables',NULL,'fruit-veg.jpg',3,1),(4,'Test L1',NULL,'categories-default.jpg',4,0);

INSERT INTO `categories_2` VALUES (1,'Beef','Feef-fr',1,1,1),(2,'Lamb',NULL,2,1,1),(3,'Pork',NULL,3,1,1),(4,'Veal',NULL,4,1,1),(5,'Poultry-Fowl',NULL,6,1,1),(6,'Sausages and Bacon',NULL,5,1,1),(7,'Salmon',NULL,8,1,2),(8,'Flat Fish',NULL,9,1,2),(9,'Common Fish',NULL,10,1,2),(10,'Squid family',NULL,11,1,2),(11,'Shellfish',NULL,12,1,2),(12,'Tuna',NULL,13,1,2),(13,'Other Fish',NULL,14,1,2),(14,'TEST L2',NULL,7,0,1);

INSERT INTO `categories_3` VALUES (1,'Specialist Beef','Specialist Beef-fr',1,1,1),(2,'Wagyu',NULL,2,1,1),(3,'Japanese Wagyu',NULL,3,1,1),(4,'Other Beef',NULL,4,1,1),(5,'All Lamb',NULL,6,1,2),(6,'All Pork',NULL,5,1,3),(7,'All Veal',NULL,7,1,4),(8,'All Poultry-Fowl',NULL,11,1,5),(9,'Pork Sausages',NULL,8,1,6),(10,'Other meats',NULL,9,1,6),(11,'Bacon',NULL,10,1,6),(12,'All Salmon',NULL,12,1,7),(13,'All Flat Fish',NULL,13,1,8),(14,'All Common Fish',NULL,14,1,9),(15,'All Squid family',NULL,15,1,10),(16,'All Shellfish',NULL,16,1,11),(17,'All Tuna',NULL,17,1,12),(18,'All Other Fish',NULL,18,1,13),(19,'TEST L3',NULL,999,0,14);

SELECT * FROM categories_2;

SELECT * FROM categories_3;

'text', C1.Category1Name,

'nodes', JSON_ARRAYAGG( JSON_OBJECT( 'id', C2.Category2ID,

'text', C2.Category2Name,

'nodes', C2.nodes,

'class', 'nav-level-2',

'href', 'admin-categories-2.php'

)

),

'class', 'nav-level-1',

'href', 'admin-categories-1.php'

) AS JSON

FROM categories_1 C1

LEFT JOIN ( SELECT C2.Category1ID,

C2.Category2ID,

C2.Category2Name,

JSON_ARRAYAGG( JSON_OBJECT( 'id', C3.Category3ID,

'text', C3.Category3Name,

'class', 'nav-level-3',

'href', 'admin-categories-3.php'

)

) as nodes

FROM categories_2 C2

LEFT JOIN categories_3 C3 ON C3.Category2ID = C2.Category2ID AND C3.Category3Status = 1

WHERE C2.Category2Status = 1

GROUP BY C2.Category2ID

) C2 ON C2.Category1ID = C1.Category1ID

WHERE C1.Category1Status = 1

GROUP BY C1.Category1ID

ORDER BY C1.Category1Order

;

SELECT C2.Category1ID,

C2.Category2ID,

C2.Category2Name,

JSON_ARRAYAGG( JSON_OBJECT( 'id', C3.Category3ID,

'text', C3.Category3Name,

'class', 'nav-level-3',

'href', 'admin-categories-3.php'

)

) as nodes

FROM categories_2 C2

LEFT JOIN categories_3 C3 ON C3.Category2ID = C2.Category2ID AND C3.Category3Status = 1

WHERE C2.Category2Status = 1

GROUP BY C2.Category2ID

SELECT C2.Category1ID,

C2.Category2ID,

C2.Category2Name,

JSON_ARRAYAGG( JSON_OBJECT( 'id', C3.Category3ID,

'text', C3.Category3Name,

'class', 'nav-level-3',

'href', 'admin-categories-3.php'

)

) as nodes

FROM categories_2 C2

LEFT JOIN categories_3 C3 ON C3.Category2ID = C2.Category2ID AND C3.Category3Status = 1

WHERE C2.Category2Status = 1

GROUP BY C2.Category2ID

SELECT C2.Category1ID,

C2.Category2ID,

C2.Category2Name,

JSON_OBJECT( 'id', C3.Category3ID,

'text', C3.Category3Name,

'class', 'nav-level-3',

'href', 'admin-categories-3.php'

)

as node

FROM categories_2 C2

LEFT JOIN categories_3 C3 ON C3.Category2ID = C2.Category2ID AND C3.Category3Status = 1

WHERE C2.Category2Status = 1