Page 1 of 1

Computing the aggreate value

Posted: Mon Jun 26, 2006 7:31 am
by namitjung
Hi all,

I am developing an ecommerce site on which category hireachy is infinitive.I mean, under one category many subcategories can be created and under that subcategory other subcategories can be created. From the admin side product can be added and it is can be assigned to any available category..


I have problem with computing the number of products which exists in one category.Since there are many child categories it should compute the number of products exists on that category too...

I would like to explain it as follows: category which has parent id 0 is the root category

Code: Select all

Category:

categoryid          categoryname         parentid
1                         A                             0
2                         B                             0
3                         Child A                    1
4                         Child B                    3
5                         Child C                    4
So category hireachy will be

A>>Child A>>Child B>>Child C
B

Code: Select all

Products

productid              Product Name           Categoryid
1                            PA                             1
2                            PB                             3
3                            PC                              4
4                            PD                             5
Here product "PA" exists under "A" category, PB in "Child A",PC in "Child B" and PD in "Child C"

Now if we have to calculate the total products from Category A it should return 4, since other three categories are child category of this category....and each category holds the one product.

I tried to use join,but since category hireachy is not known i couldn't able to use that..


Thanx in advance

Posted: Mon Jun 26, 2006 7:46 am
by namitjung
Should i use sql statement first and again use another statement infinetly?

Posted: Mon Jun 26, 2006 8:22 am
by CoderGoblin
If you do not mind changing your database structure:
Storing Hierarchical Data in a Database is a good starting point for storing a tree in a database.

Using left and right values allows you to get the number of category decendants easily (in fact the method is listed on that page).

If the category table "cat_list" holds the number of products you can have a select similar to

Code: Select all

SELECT a.cat_id, sum(b.no_produkt) AS total_produkt, (a.rgt - a.lft - 1) / 2 AS total_cats
   FROM cat_list a, cat_list b
  WHERE b.lft >= a.lft AND b.lft <= a.rgt
  GROUP BY a.cat_id, a.rgt, a.lft;
to get the totals.

Using parent ref means the only method you can use is recursion (normally using PHP rather than SQL).

Posted: Mon Jun 26, 2006 9:03 am
by xpgeek
The best way use dbtree library - it include good examples and also include admin area.
http://dev.e-taller.net/dbtree/