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', 'Artist Desc', 'Album')
# Fetch the columns in scalar context.
$columns = get_columns (
order_by => ['NAME', 'ARTIST DESC', 'ALBUM'],
show_ascending => 1,
uc_direction => 1,
);
# 'NAME ASC, ARTIST DESC, ALBUM ASC'
# Fetch the columns as a name array and direction hashes.
@columns = get_columns (
order_by => 'name, artist deSc, album',
name_direction => 1,
numeric_direction => 1,
);
# (['name','artist','album'],
# {name=>1, artist=>0, album=>1},
# {name=>'', artist=>'deSc', album=>''})
# Output a "column direction" array.
@columns = col_dir_list (\@column_names, \%direction);
# ('name', 'artist desc', 'album')
# Convert the numeric directions to SQL keywords.
%direction = to_asc_desc (
\%direction,
show_ascending => 1,
);
# (name=>'asc', artist=>'desc', album=>'asc')
# Single toggle resort in array context.
@order = toggle_resort (
show_ascending => 1,
selected => 'artist',
order_by => ['name', 'artist', 'album'],
);
# ('artist asc', 'name asc', 'album asc')
# Nested toggle resort in scalar context.
print scalar toggle_resort (
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. Off by default.
name_direction => Return references to the column names and their directions. Off by default. Only makes sense in array context.
numeric_direction => Return Boolean column directions, instead of asc/desc. Off by default. Only makes sense with the name_direction flag on.
uc_direction => Render any new alpha column direction in uppercase. Off by default.
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 do not include 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.
get_columns
@columns = get_columns (
order_by => $order_clause_or_list,
uc_direction => $w,
show_ascending => $x,
name_direction => $y,
numeric_direction => $z,
)
$columns = get_columns (
order_by => $order_clause_or_list,
show_ascending => $x,
uc_direction => $y,
)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 as numeric hash and a hash of the exact sort directions passed in.
This function optionally takes Boolean flags affecting the returned data structure. These are:
show_ascending => Expose the asc column directions. Off by default.
name_direction => Return references to the column names and their directions. Off by default. Only makes sense in array context.
numeric_direction => Return Boolean column directions, instead of asc/desc. Off by default. Only makes sense with the name_direction flag on.
uc_direction => Render any new alpha column direction in uppercase. Off by default.
col_dir_list
@columns = col_dir_list (\@columns, \%direction);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.
to_asc_desc
%direction = to_asc_desc (
\%direction,
show_ascending => $x,
uc_direction => $y,
)Return column directions as alpha keywords in place of their numeric eqivalents.
If the direction hash contains any alpha (asc/desc) values, the function uses those by default.
This function takes a reference to a direction hash and optional flags to control the display of the asc keyword and new direction names. These flags are:
show_ascending => Expose the asc column directions. Off by default.
uc_direction => Render any new alpha column direction in uppercase. Off by default.
DEPENDENCIES
None.
TODO
Add functions for different kinds of resorting?
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.