codeigniter nested categories.
Many days ago when I was thinking about my CMS, the latest upgrade was very important because it let me focus only on the logic and avoid repeating myself again and again.
So I made a blueprint for the most important modules which was repetitive for almost every project I had.
One of them was Categories module, often clients ask news website which had many categories like (Politics- Sports- Technology- Weather- Media- Regional News), on the other hand, some clients want a website for his company which had news also but there is no categories, just the company news.
This I put in my head when was thinking for categories module, I was it a separate module because avoid remove categories with some clients and put it on others, also I kept in mind to build it dynamically as possible because I had no idea what categories level might be there, sometimes one level like (sports category) sometimes two levels like (sports ->football) or even multiple levels of subcategories.
After googling it, I figure out there are four options to manage Hierarchical Data (aka recursive Hierarchical Data), all these options back to SQL Structures:
1- Adjacency list
2- Nested set
3- Closure table (aka bridge table)
4- Materialized path (path enumeration)
Every one of these options has its pros and cons, there are many articles out there discussed these options in details which I will not do in this article, I already chose Adjacency list because I believe it is the simplest and easiest way to deal with Hierarchical data, the only concern to keep in your mind that this structure is fit to small and medium tree data levels, if you have a big project with deep Hierarchical Data levels then I recommended using Materialized path because it was much simpler than others
Every record knows its immediate parent
-Simple, easy to understand schema
-Easy to query for children
-Easy to update
-Deep trees cause problems
-Difficult to query subtree
/* Graph Table A node, parent / \ A, B E B, A / \ C, B C D D, B E, A */
Adjacency List isThe simplest option Chosen as a default by many developers.
CREATE TABLE nodes( id SERIAL PRIMARY KEY, parent_id BIGINT NOT NULL, text varchar(10) NOT NULL); -- add foreign keys for referential consistency -- add unique index to avoid duplication
Inserting records is straightforward:
INSERT INTO nodes(id, parent_id, text) VALUES (1, 1, 'A'), -- Root. (2, 1, 'B'), (3, 2, 'C'), (4, 2, 'D'), (5, 1, 'E');
Retrieving data becomes problematic although.Say we want to
SELECT whole hierarchy:
SELECT * FROM nodes where parent_id = 1; id | parent_id | text ----+-----------+------ 1 | 1 | A 2 | 1 | B 5 | 1 | E
But only the immediate children get fetched instead whole “tree”. The Same issue exists for both UPDATE and DELETE queries.
Adjacency List model is sufficient data model. But the lack of recursive querying support (i.e. in MySQL) leads to complex code, and because Adjacency List model alone is often not enough as a real world solution, the PHP comes to fill the gap.
PHP can make a recursive loop to SQL query to generate the full query, you can do this with yourself or you can use CodeIgniter library.
I recommend using this library because it is a full code example with Simple implementation with the nestedSortable plugin.
The library based on two table adjacency_groups and adjacency_lists, in my category module I used them like this :
1- Adjacency_groups for saving the module name which need categories as example (articles categories -news categories -products categories)
2- Adjacency_lists for saving categories related to specific module as example (sports news-media news-political news ,etc.)