[Phpgroupware-developers] Category sorting

[Randy Smith]

Hi all,

I have run into the following problem when phpGroupWare 0.9.12 (with a mysql 
backend) tries to sort Categories. The problem, basically, is that the 
categories are not sorted properly.

For example:
Let's say that I have three main (or top level) categories named TLC1, TLC2 
and TLC3 that have been entered on opposite order. i.e. TLC 3 was entered 
first and TLC1 was entered last. When I pull up a list of categories through, 
for example, the Address Book, it will list them in the order entered. The 
reason, as near as I can tell, is that the SQL query uses the the following 
segement to sort the categories (see 
phpgeapi/inc/class.categories.inc.php:114):
 order by cat_main, cat_level, cat_name asc

The problem comes in that cat_main and cat_level are both integers.
mysql> describe phpgw_categories;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| cat_id          | int(9)       |      | PRI | 0       | auto_increment |
| cat_main        | int(9)       |      |     | 0       |                |
| cat_parent      | int(9)       |      |     | 0       |                |
| cat_level       | int(3)       |      |     | 0       |                |
...

The 'main' categories all have their own 'cat_id' set for 'cat_main'. This 
means that the list will always be "sorted" by the order the categories were 
entered. This idea makes sense, in a way, since sub categories have their 
'cat_main' set to the 'cat_id' of its parent. This forces all of the sub 
cetegories to be listed below their respective parents. (They are also 
sorted.) I think this is a Good Thing.:-) However, I would also like the rest 
of the categories to be sorted.

I have worked up some pseudo-code that looks to me that it should work but I 
don't know enough about the internals of phpGroupWare to implement it. I 
would greatly appreciate it if one of you fine people would give me a hand.

Now to the pseudo-code. For the interested, this is an iterative, depth-first 
tree traversal with nodes on the same level sorted by name.

NB: This looks a lot like Perl because that's what I'm most comfortable with. 
Sorry.

// Get the main categories. Assume that the elmts of this list are
// the assoc. lists that are created in return_array().
@main = "select * from phpgw_categories where cat_level = 0
        where cat_level = 0;"; // A
$length = length(@main); // num elmts in @main i.e. num cats
for ($i = 0; $i < $length; $i++) {
        @L = "select * from phpgw_categories
                where cat_main=$main[$i]['id']
                and cat_level=($main[$i]['level']+1)
                order by name;"; // B
        // @L is now have a list of the direct sub categories
        // of the category pointed to by $main[$i].
        // What I'm doing now is inserting the new list after
        // the current category.
        @main = (@main[0..$i], @L, @main[$i+1..$length-1]); //C
        // Get the new length of @main
        $length = length(@main);
}
// @main now has all of the categories.
return @main;

Let's look at an example that will demonstrate what this list should look 
like as the above algorithm executes.

Let's assume that we have three top level categories C1; C2 and C3.
C1 has three sub categories C1,1; C1,2 and C1,3.
C1,1 has two sub categories C1,1,1 and C1,1,2

After line A, @main should be (C1; C2; C3) because the top level categories 
all have a level of 0. (These are ordered by their respective names.)

After line B, @L should be (C1,1; C1,2; C1,3), $i = 0 and $length = 3.

Line C inserts @L in to @main so @main should look be (C1; C1,1; C1,2; C1,3; 
C2; C3). We get the new length next to make sure that we go through the 
entire list. $length should now equal 6.

Now we come to the end of the loop and $i gets incremented to 1. @main[1] is 
C1,1, the first sub category of our main node C1. After we go through the 
process for C1,1 , @main = (C1; C1,1; C1,1,1; C1,1,2; C1,1; C1,3; C2; C3).

$i now = 2 and we repete the process for C1,1,1. However, it has no sub 
categories so @L is empty. Inserting it has no effect on @main. (I suppose a 
test could be built in to skip the insertion if @L is empty.)

We continue on for the rest of the categories but, since none of them have 
sub categories, @main doesn't change. We now have the list of categories 
sorted by name at each level.

Sorry for the long message. Again, I ask if one of y'all can help me work 
this into the PHP the phpGroupWare needs. Thank you.

-- 
Randy Smith
Amigo.Net Systems Administrator
1-719-589-6100 x 4185
http://www.amigo.net/