/ 
SQL-OrderBy-0.07
River stage zero No dependents
/ SQL::OrderBy

NAME

SQL::OrderBy - Transform an SQL ORDER BY clause.

SYNOPSIS

use SQL::OrderBy;

# Fetch the columns in array context.
@columns = get_columns (
    order_by => 'name, artist desc, album',
);
# ('name asc', 'artist desc', 'album asc')

# Fetch the columns in scalar context without the asc keyword.
$columns = get_columns (
    order_by => ['name', 'artist desc', 'album'],
    show_ascending => 0,
);
# 'name, artist desc, album'

# Fetch the columns as a name array and numeric direction hash.
@columns = get_columns (
    order_by => 'name, artist desc, album',
    name_direction => 1,
    numeric_direction => 1,
);
# (['name','artist','album'], {name=>1, artist=>0, album=>1})

@columns = col_dir_list (\@column_names, \%directions);

%directions = num2asc_desc (\%directions, 0);

# Toggle resort in array context.
@order = toggle_resort (
    selected => 'artist',
    order_by => ['name', 'artist', 'album'],
);
# ('artist asc', 'name asc', 'album asc')

# Toggle resort in scalar context without the asc keyword.
print scalar toggle_resort (
    show_ascending => 0,
    selected => 'time',
    order_by => scalar toggle_resort(
        selected => 'artist',
        order_by => scalar toggle_resort(
            selected => 'artist',
            order_by => 'name asc, artist asc, album asc'
        )
    )
);
# 'time, artist desc, name, album'

ABSTRACT

Resort and toggle (ascending/descending) table columns given an SQL ORDER BY clause.

DESCRIPTION

This package simply transforms an SQL ORDER BY clause by moving or adding column names and toggling their ascending/descending state.

Note that this is intentionally naive code, in that no database integrity checking is done.

FUNCTIONS

toggle_resort ()

toggle_resort(
    order_by => $order_clause_or_list,
    selected => $column_name,
)

This function takes two arguments provided as named parameters: an SQL "ORDER BY" clause as either a string or array reference and a column name.

The selected column name is moved or added to the beginning of the clause with its sort direction exposed. If this column is the first column of the list, its sort direction is flipped between ascending (asc) and descending (desc).

Note that the state of the sort is maintained, since the selected column name is the only one that is fondled.

In a scalar context, this function returns the clause as a (CSV) string. In an array context, this function returns a list of column names with their respective sort directions.

This function optionally takes Boolean flags affecting the returned data structure. These are:

show_ascending => Expose the asc column directions. Defaults on (1).

name_direction => Return references to the column names and their directions. Defaults off (0). Only makes sense in array context.

numeric_direction => Return Boolean column directions, instead of asc/desc. Defaults off (0). Only makes sense with the name_direction flag on.

This implements an essential feature for GUI environments, where the user interacts with a table by sorting and resorting with a mouse and "toggle button column headings" during an interactive search refinement session.

* If you leave off the selected argument, this function will simply return the clause with sort directions for each column name. That is, no "toggling" or moving is done.

* Currently, this function is not exported by default.

get_columns ()

@columns = get_columns (
    order_by => $order_clause_or_list,
    show_ascending    => $x,
    name_direction    => $y,
    numeric_direction => $z,
)

$columns = get_columns (
    order_by => $order_clause_or_list,
    show_ascending => $x,
)

This function simply returns a well formed order by clause or list. It can accept either a string or array reference for the order_by argument.

In a scalar context, this function returns the clause as a (CSV) string. In an array context, this function returns a list of column names with their respective sort directions.

This function optionally takes Boolean flags affecting the returned data structure. These are:

show_ascending => Expose the asc column directions. Defaults on (1).

name_direction => Return references to the column names and their directions. Defaults off (0). Only makes sense in array context.

numeric_direction => Return Boolean column directions, instead of asc/desc. Defaults off (0). Only makes sense with the name_direction flag on.

col_dir_list ()

@columns = col_dir_list (\@columns, \%asc_desc);

Return an array of column names with their respective directions concatinated.

This function takes a reference to an array of column names and a reference to a direction hash.

num2asc_desc ()

%directions = num2asc_desc (\%directions, $show_asc)

Return directions as "asc" and "desc" in place of their numeric eqivalents.

This function takes a reference to a direction hash and an optional flag to control the display of the asc keyword.

DEPENDENCIES

None.

TODO

Add functions for different kinds of resorting?

Add the ability to return the order by clause without altering the case (or display) of column names and directions.

HISTORY

See the Changes file in this distribution.

AUTHOR

Gene Boggs, <cpan@ology.net>

COPYRIGHT AND LICENSE

Copyright 2003 by Gene Boggs

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.