Hi guys,
I'm using the Adjacency List Model to create categories, and it works perfectly.
When retrieving articles in a certain category (for example electronics), I would like to also retrieve the articles in the sub categories (for example electronics->cameras, or even electronics->cameras->camera lenses).
The way I am doing it now is pulling from the DB all the category id's of the sub categories of electronics, and finding all articles with a category_id in this list.
This seems to me very inefficient and time-consuming, since this could result in many queries to retrieve these sub categories.
Another way I thought of doing this is having every article associated with the whole category tree (for example an article about camera lenses will also be associated with the camera and electronics categories in MANY_MANY table), and when I retrieve all articles in electronics it will also appear.
This would add a lot of redundant data to the database though, as I might have to store 3 or 4 categories for each article. Also, it would complicate actions like moving an article to another category.
Is this the right way to go? Or is there a better/simpler way that I have not thought of?
Any help appreciated!