I’m designing a system where we will have three category levels. I’ve been reading about how to store and retrieve hierarchical data in MySQL but now I’m starting to wonder if this will be difficult to do using Yii as I plan to do. I’m new to Yii and still messing with my table designs at this point. I’m really not even building anything in Yii at the moment since I’m setting up the tables and fields and creating a backend data entry/administration database in MS Access.
My first question, is there some way that Yii is already designed to handle hierarchical category systems with 3 or 4 levels?
I’m going to post some example queries using my current design. I’m wondering if retrieving data using these queries and displaying it in my views will be mostly a manual process? What part of this will the Yii framework do for me? Any documentation or examples about using you’re own queries and writing your own views to format and display the resulting data?
Select All Products in a Given CategoryID:
SELECT DISTINCT product.pname
FROM product INNER JOIN cat_product ON product.product_id = cat_product.product_id
WHERE (((cat_product.category_id) In (9,7)));
Select all Products in a Given Category Name:
SELECT DISTINCT product.pname
FROM (product INNER JOIN cat_product ON product.product_id = cat_product.product_id) INNER JOIN nested_category ON cat_product.category_id = nested_category.category_id
WHERE (((nested_category.cname) In ('CD Players','MP3 Players')));
Get Count of Products in Each Category/SubCategory:
SELECT parent.cname, Count(product.pname) AS CountOfpname
FROM nested_category AS parent, (cat_product INNER JOIN product ON cat_product.product_id = product.product_id) INNER JOIN nested_category AS node ON cat_product.category_id = node.category_id
WHERE (((node.lft) Between [parent].[lft] And [parent].[rgt]) AND ((node.category_id)=[cat_product].[category_id]))
GROUP BY parent.cname
ORDER BY Count(product.pname) DESC;
I’m sorry about the way that MS Access adds unnecessary parenthesis. I didn’t take the time to clean them up.
It doesn’t have to be a manual process, thank Yii! While it’s true that Yii has the notion of a statistical relationship between tables, I find that once the queries get a bit complex, it’s far simpler to use database views than it is to dig into the active record and create complex relationships.
I’ve been using MySQL views, in a limited fashion, with Yii, for cases just like this. My procedure has been:
Create the view in MySQL
Use gii to generate a model from the database view. Modify the model’s
search(), attributeLabels() functions to taste
Use gii to generate CRUD for the model
REMOVE the update.php, create.php, _form.php views, because you can’t use them.
Most views are non-updateable in terms of the database. But views are super-handy
for browsing.
Modify the generated admin.php view, and the controller, to taste
Regarding whether or not Yii has anything built-in to manage tiered hieararchies, as you suggest, I don’t have the answer to that. Am curious to find out, though, so please post your findings.
It appears to me that the extension deals primarily with management tasks like adding and deleting leafs and nodes. This really is the complicated part so that’s good. However, I couldn’t find anything in the documentation about what is stored in several of the fields, namely the ‘root’ and ‘level’ fields. These fields are not a part of the standard nested model set design (as shown on MySQL’s site) so I’m a little lost. If I use the extension it won’t matter. However, I figured I would build my category management on the back end which will be a Windows App so I’m going to need to write my own logic. If I do this I suppose I don’t even need this nestedsetbehavior extension. Except I did think it would be a good idea to design it so that this extension would work if I need to use it in the future.
