Forum OpenACS Q&A: Help with ad_categorization_widget ad_categorize

1: Help with ad_categorization_widget ad_categorize_row procs

Posted by amit singh on 08/27/01 04:08 PM

Has anyone used the two procs

ad_categorization_widget 
ad_categorize_row 

Being a newbee i know their function but i was not able to incorporate
them into any productive use. Can someone help on this.

2: Response to Help with ad_categorization_widget ad_categorize_row procs (response to 1)

Posted by Walter Smith on 08/30/01 08:36 PM

I have done some work on the widget, although I haven't actually used ad_categorize_row. The widget wasn't showing the correct category hierarchy, so I was working on fixing it. I never posted my fix, because I didn't completely resolve the problems. I did manage to get it to display the relationships between categorization and subcategorization levels, although it still doesn't sort them alphabetically.

I changed the code for ad_categorization_widget from this:

    # Fetch the entire category hierarchy.
    #
    set selection [ns_db select $db "select c.category_id, lpad(' ', 12*(category_hierarchy_level(c.category_id, NULL, 0) - 1),' ') as indent, c.category, c.category_type
from categories c
 order by category_hierarchy_sortkey(c.category_id, NULL,'')"]

to this:

    # Fetch the entire category hierarchy.
    #
    set selection [ns_db select $db "
    SELECT c.category_id, 
           lpad(' ', 12*(category_hierarchy_level(c.category_id, '', 0) - 1),' ') as indent, 
           c.category, 
           c.category_type
    FROM   categories c,
           category_hierarchy h,
           category_hierarchy h1
    WHERE  c.category_id = h.child_category_id
           AND h1.parent_category_id IS NULL
           AND category_hierarchy_level(h.child_category_id, h1.child_category_id, 0) is not null
    ORDER BY category_type,
             category_hierarchy_sortkey(h.child_category_id, h1.child_category_id, ''),
             category_hierarchy_level(c.category_id, '', 0)"]

My formatting makes it a little more difficult to see, but the changes are

1) In the SELECT statement, changing the "NULL" in the call to category_hierarchy_level.
2) Adding to and changing the order of the ORDER BY statements.

I am still working on getting everything to sort alphabetically, but I believe that will involve creating another PL/PGSQL function.

The "general-links" module uses the categorization widget and the content mapping feature, and it might be a good example for you to start with. It seems to work correctly, so if you add a link with associated categories it will insert it into "site_wide_category_map".

3: Response to Help with ad_categorization_widget ad_categorize_row procs (response to 1)

Posted by Cathy Sarisky on 09/02/01 09:46 AM

Here's a hack that gives me correctly sorted and ordered links (and works in other modules too):


create function alpha_category_hierarchy_sortkey(integer, integer, char)
returns char as '
DECLARE
        v_category_id alias for $1;
        v_start_id alias for $2;
        v_sortkey alias for $3;
        v_parent_id integer;
        v_parent_name varchar(40);
        v_category_name varchar(40);
        BEGIN

        select parent_category_id into v_parent_id from category_hierarchy where child_category_id= v_category_id;
        select category into v_parent_name from categories, category_hierarchy where child_category_id = v_category_id AND c
ategory_id = v_parent_id;
        select category into v_category_name from categories, category_hierarchy where category_id = v_category_id;

        IF v_category_id = v_start_id
        then return (v_category_name::char || v_sortkey);
        end if;

        if v_parent_id is null
        then return NULL;
        end if;
                                                                                                
        return alpha_category_hierarchy_sortkey(v_parent_id, v_start_id, v_category_name::char || v_sortkey) ;
END;
' language 'plpgsql';

I'm calling this function with code like:

SELECT c.category_id, c.category, c.category_type, url, link_title, n_ratings, round(avg_rating, 1) as avg_rating,
        category_hierarchy_level(c.category_id, $start_with_proc, 0) - 1 as indent, 
        gl.link_id
  FROM categories c, category_hierarchy h, category_hierarchy h1, general_links gl, site_wide_category_map swm
  WHERE c.category_id= h.child_category_id
    AND category_hierarchy_level(h.child_category_id, h1.child_category_id, 1) is not null
    AND gl.link_id = swm.on_what_id
    AND swm.category_id = c.category_id
    $where_clause_for_search_query
    $where_clause_for_section
  ORDER BY alpha_category_hierarchy_sortkey(h.child_category_id, h1.child_category_id, ''), link_title

4: Response to Help with ad_categorization_widget ad_categorize_row procs (response to 1)

Posted by amit singh on 11/02/01 11:13 AM

Thanks,
Things worked out for me. But in the above query how can i limit the no of general-links displayed to be not more than 5 links per categorey.

AMIT

Forum OpenACS Q&A: Help with ad_categorization_widget ad_categorize_row procs