Is there a function already in Links somewhere which calculates the number of subcategories and/or links a particular category has?

I'd like to put this into sub, but don't want to re-invent the wheel if I don't have to.

Example:

number_of_subcategories ($category_id);

links_in_subcategories($category_id);


http://www.iuni.com/...tware/web/index.html
Links Plugins

>>
links_in_subcategories($category_id);
<<

my $total = $DB->table('CatLinks')->count( { CategoryID => $category_id } );

Im stuck on the first one...and too tired ot think...sorry :(

Edit: ...and spell.

Thanks Paul... no worries. Tommorow is another day. So they say anyways!

I can get started with the one you gave me anyways :-)


http://www.iuni.com/...tware/web/index.html
Links Plugins

Paul, I know you were tired when you replied:

Won't this only count the links in one category, and not all of its subcategories as well?

my $total = $DB->table('CatLinks')->count( { CategoryID => $category_id } );


http://www.iuni.com/...tware/web/index.html
Links Plugins

Ok, this is really starting to baffle me this one.

I know links must already calculate these somewhere, but I can't work out how or where

The only thing I have found is this recursive delete from category.pm:

Code:

# Recursively delete all the subcategories and the original category.

my $catlnk_db = $DB->table ('CatLinks');

my $link_db = $DB->table ('Links');

my $count = 0;

# Remove all the categories.

my $children = $self->children ($id);

push @$children, $id;

foreach my $child (@$children) {

my $sth = $catlnk_db->select ( ['LinkID'], { CategoryID => $child } );

if ($sth->rows) {

while (my ($link) = $sth->fetchrow_array) {

if ($catlnk_db->count ( { LinkID => $link }) == 1) {

$link_db->delete ($link);

$nol--; # Counts get handled in link module.

}

}

}

$self->SUPER::delete ($child) and $count++;

}

# Clear out the cache as the heiarchy has changed.

$self->_clear_cache;

http://www.iuni.com/...tware/web/index.html
Links Plugins

Just a few ideas for the algorithm:
1) As I remember, Links count of each category are stored as plain numbers in the category table. Unnecessary to recount them. (I don't know how accurate is this info & how often is recounted)
2) You should only list subcategory IDs of the current category, then just add them to each other (summarize).
3) You just got the links number in subcategories and below. If you want count links in current category, too, just add link number of current category ID.

That's all the algorithm logic.
You just need to do it for yourself

You are getting home in Perl & SQL quickly, so there will be no problem solving it (I hope so). If not, we are still here to help (if we can).

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Isn't this info already stored in the category table?

Just thinking about solution...

As I remember, (Sub)Categories are only referenced by the ID of parent category.
If we have 1000 subcategories in 100 level depth, we have to do even 1000 database queries to find out the parent of each other. Correct me, if I'm wrong...
1000 queries to find just the subcategories each time, seems waste of time.
Hmmm. Well, not really, because you can fetch them from database in one step, then do the parent mapping in Perl memory, which is waaay faster. However, the idea to cache the results, would be still useful to save some CPU resource.

If you plan to use, display the subcategories info very often, seems a good idea to cache (store) the values, results.
The solution, and what you would need to store, would depend on several parameters:
- how often you need the subcat number info,
- how many times you need to use it,
- etc...

For just the number_of_subcategories() function, I would store the result numbers, especially if these are displayed, recounted often, e.g. to display on main page or category page.
Also you have to pay attention to refresh the cached items, when a related info is changed, e.g. category deleted, moved, etc...

Best regards,
Webmaster33

---

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Wow... great suggestions webmaster!

I was thinking the same thing about the number of database fetches being a performance issue. I am going to rename the column "haschildren" to "number_of_children". This not only gives a place to store the information, but is more useful.

I could get by with doing this once (during a quiet time on my site), then performing all my cleanup functions. I was hoping that all this effort in making this work, would be useful to someone else though... so if I can make it so it is a low-performance drain, and a little more dynamic that would be a bonus. For now, I just want something to go through the database and hopefully create this table.

I will study your above posts some more...


http://www.iuni.com/...tware/web/index.html
Links Plugins