Difficult Count Assigment

[tito]

Hi,

I have 2 tables, articles and categories
Everytime an article is created a category or subcategory is assigned to it

My category table is structured to have subcategories.

catid | parentid | parentlist
1      |0               |1
3      |1               |3,1
4      |3               |4,3,1

catid 1 with parentid 0 is a top category.

How can I retrieve article count and assign them to their cat and parents.

e.g if I set catid 4 to an article
I'd like to adjust the count in catid 4, 3 and 1

Instead of doing a catcount + 1, I had rather select count(*) FROM articles WHERE catid IN(parentlist) and then update the appropriate categories so the top level has all count from itself + its sublevels, the sublevel has all count in itself + its sublevels, and finally the last sublevel has only its count.

Example: If I add an article and assign it to subcat 4
then subcat updates count for all articles with subcat 4
all articles having subcat 4,3 in subcat 3
and finally the top cat will have the count in 4,3,1
I want to do it like that because it's more accurate as sometimes articles get deleted or get moved to other category.

Thank you.

[sneakyimp]

Your category table is confusing to me. why would each cat have its own id among its parent list? Doesn't that make queries difficult because when you create a new category, you have to know what its ID is going to be because you need to put it in the parent list?

It looks like you are dealing with hierarchical data (like a tree structure...or folders-in-folders type thing). To define this type of structure, all you really need to store for a category is its parent id. Operating on this type of data is painful because you often have to create recursive functions (functions that call themselves) or create loops.

For example, to make a breadcrumb for a given category, you have to keep fetching parent ids until you get parent id zero. Or to search a particular category and all its subcategories, you might have to fetch all the children of your category and all the children of those children, etc.

To count the number of articles in a given category, you'd need one of these loops...unless you use a 'nested set' approach. This article is a pretty good intro. The 2nd page describes nested sets. With nested sets you can apply changes to a category and all its subcategories (or all its parent categories) more effectively. The downside is that they are confusing to maintain.

[felixtgomezjr]

I believe you want to store the article count? I would prefer just to use query statement when I need to get the article count.