Forum OpenACS Q&A: order by at file-storage

Collapse
Posted by David Kuczek on
At the 3.2.5 file-storage the folders and the files are ordered by the
sort_key, which gets generated via the procedure fs_order_files and
there via the select which gets ordered by fs_connect_by(file_id).

This way the folders and its files are ordered by their creation date.

After that the sort_keys are updated in the fs_files table.

I am thinking of adding an extra row to fs_files maybe called
sort_key_by_file_title which displays all the folders and their files
ordered by file_title and not by creation time...

I would have to write another procedure similar to fs_connect_by that
orders the file_ids by their file_title and their dependency.

create function fs_connect_by(integer) returns text as '

declare

        id              alias for $1;

        pid            integer;

BEGIN

        select into pid parent_id from fs_files where id = file_id;

        IF pid is null

        THEN

                return fs_gen_key(id);

        ELSE

                return fs_connect_by(pid) || ''/'' || fs_gen_key(id);

        END IF;

END;

' language 'plpgsql';

I am stuck right here... Does anyone have an idea?

Collapse
Posted by David Kuczek on
Alright....

I coded a new function for fs_connect_by:

It sorts the file-storage not chronologically, which is REALLY messy, but alphabetically.

Additionally to that all files inside a folder appear alphabetically IN FRONT of the sub-folders.

create function fs_connect_by(integer) returns text as '
declare
        id              alias for $1;
        pid             integer;
        title           varchar;
        folder          varchar;
BEGIN
        select into pid parent_id from fs_files where id = file_id;
        select into title lower(file_title) from fs_files where id = file_id;
        select into folder folder_p from fs_files where id = file_id;
        IF pid is null
        THEN
                return folder || ''/'' || title || ''/'' || fs_gen_key(id);
        ELSE
                return fs_connect_by_new(pid) || ''/'' || folder || ''/'' || title || ''/'' || fs_gen_key(id);
        END IF;
END;
' language 'plpgsql';


If I made a mistake, go ahead an tell me...
Collapse
Posted by David Kuczek on
This must be faster:
create function fs_connect_by_great(integer) returns text as '
declare
        id              alias for $1;
        pid             integer;
        title           varchar;
        folder          varchar;
BEGIN
        select parent_id, lower(file_title), folder_p INTO pid, title, folder from fs_files where id = file_id;
        IF pid is null
        THEN
                return folder || ''/'' || title || ''/'' || fs_gen_key(id);
        ELSE
                return fs_connect_by_new(pid) || ''/'' || folder || ''/'' || title || ''/'' || fs_gen_key(id);
        END IF;
END;
' language 'plpgsql';
Collapse
Posted by David Kuczek on
Sorry that I spam you with this, but I have made some silly mistakes!!

This must be right now:
create function fs_connect_by(integer) returns text as '
declare
        id              alias for $1;
        pid             integer;
        title           varchar;
        folder          varchar;
BEGIN
        select parent_id, lower(file_title), folder_p INTO pid, title, folder from fs_files where id = file_id;
        IF pid is null
        THEN
                return folder || ''/'' || title || ''/'' || fs_gen_key(id);
        ELSE
                return fs_connect_by(pid) || ''/'' || folder || ''/'' || title || ''/'' || fs_gen_key(id);
        END IF;
END;
' language 'plpgsql';
Collapse
Posted by Don Baccus on
I certainly prefer alphabetic sorting, and that's what most users will be familiar with ... how about others?  You can always submit this as a patch in the SDM ...  it's hard to say when we'll roll out a 3.2.6 but it will happen.