Forum OpenACS Q&A: Using tree_sortkey and alphabetizing?

Collapse
1: Using tree_sortkey and alphabetizing?
Posted by Jade Rubick on 07/28/03 11:51 PM
I'm trying to sort a list of hierarchical data for the project-manager package. If you look at:

https://openacs.org/projects/dotwrk/project_management/user_interface

you'll see what I'm trying to do. I'd like to have the list of projects be alphabetized.

In Oracle, this was possible, but hackish. Is it possible in Postgres?

I'll contribute any working solutions to my tree_sortkey documentation for posterity. 😊

Collapse
2: Re: Using tree_sortkey and alphabetizing? (response to 1)
Posted by Jade Rubick on 07/28/03 11:52 PM
Here is the SQL:

        SELECT
    p.item_id,
        p.project_id,
    repeat(:indent_pattern, (tree_level(p.tree_sortkey) - 5)* :indent_factor) as indent,
        p.parent_id as folder_id,
    p.object_type as content_type,
    p.project_name,
    p.project_code,
    to_char(p.planned_start_date, 'YYYY MM DD') as planned_start_date,
    to_char(p.planned_end_date, 'YYYY MM DD') as planned_end_date,
    p.ongoing_p
        FROM pm_projectsx p, cr_items i
        WHERE p.project_id = i.live_revision
        ORDER BY i.tree_sortkey
Collapse
3: Re: Using tree_sortkey and alphabetizing? (response to 2)
Posted by Jade Rubick on 07/29/03 12:14 AM
Dave suggested replacing pm_projectsx p with

(select * from pm_projectsx orderby project_name asc) p

That seemed a brilliant idea. Unfortunately, it didn't work, so I cried.