Gossamer Forum
Home : Products : DBMan SQL : Discussion :

multiple sort

Quote Reply
multiple sort
I would like to do a search and sort by multiple fields. There was a post to do this for DBMan SQL version 1 (http://www.gossamer-threads.com/...forum.cgi?post=83092), but I haven't seen a mod for the multiple sort for version 2.0.3. Any help would be greatly appreciated. Thanks.
Quote Reply
Re: [mrsnyder] multiple sort In reply to
Here's a copy from our support tickets. Everybody might be interested in using it :

You could do the same modification in Dbsql 2.x by following the
instructions below:
1. Add two new sub below into Dbsql/HTML.pm
Code:
sub generate_so_list {
#----------------------------------------------------------------------
my $name = shift;
my $tags = GT::Template->tags;
my $so = $tags->{home}->{disp}->select ( { name => $name,
values => { ASC => 'Ascending',
DESC=> 'Descending'
},
default => $tags->{home}-> {cgi}->{$name},
blank => 0 });
return $so;
}
sub generate_sb_list {
#----------------------------------------------------------------------
my $name = shift;
my $tags = GT::Template->tags;
my $c = $tags->{home}->{db}->cols;
my ($hash, $order) = ({}, []);
foreach my $col (sort {
defined ($c->{$a}->{pos}) or warn "No pos for $a\n";
defined ($c->{$b}->{pos}) or warn "No pos for $b\n";
$c->{$a}->{'pos'} <=> $c->{$b}->{'pos'}
} keys %$c) {
$hash->{$col} = $c->{$col}->{view_name} || $col;
push @$order, $col;
}
my $sb = $tags->{home}->{disp}->select ( {
name => $name,
values => $hash,
sort_order => $order,
default => $tags->{home}->{cgi}->{$name},
blank => 1 });
return $sb;
}




then add tags like :
<%Dbsql::HTML::generate_sb_list('sb1')%><%Dbsql::HTML::generate_so_list
('so1')%>
<%Dbsql::HTML::generate_sb_list('sb2')%><%Dbsql::HTML::generate_so_list
('so2')%>
.....
into your search_form template. These functions will generate
automatically select lists with name sb1,so1 ...etc from your
current collumns (or if you like you can add these HTML code directly
to the template and ignore this step)

2. Add this code (asuming you have 6 columns to sort by)


Code:

#---NEW CODE ----
$self->{cgi}->{sb} = '';
my $order = join ',' => map {$self->{cgi}->{"sb$_"}.' '.$self->{cgi}->{"so$_"}} (1..6) ;
($order) and $self->{db}->select_options ("ORDER BY $order") ;
#---END NEW CODE ----

into Dbsql/Home.pm (sub process), below the line (around line 19)
$self->{disp} = $self->{sql}->html ($self->{db}, $self->{cgi});


Cheers,
Jean@Gossamer Threads
Quote Reply
Re: [jean] multiple sort In reply to
how would you customize this so you could have a flexible number of columns to sort by? in other words, sometimes you may want to sort by 6 fields, sometimes 2 or sometimes just 1. i get a syntax error if i leave 1..6. i tried the following but get error 500:
Code:
$self->{cgi}->{sb} = '';
my $i = 1;
if ($sb2) { $i = 2; }
if ($sb3) { $i = 3; }
my $order = join ',' => map {$self->{cgi}->{"sb$_"}.' '.$self->{cgi}->{"so$_"}} (1..$i) ;
($order) and $self->{db}->select_options ("ORDER BY $order") ;