After I posted this, Tim Bunce asked "[a]nd if a table has a field named
order?" [1] and I slapped my forehead.
So I wrote a second patch (attached) which tries to actually do the
right thing now; more like what 'select()' already does.
Cheers,
[guest - Mon Feb 10 10:26:55 2003]:
Show quoted text> Recently, the Class::DBI gang wrote Class::DBI::AbstractSearch which
> uses the SQL::Abstract->search() method to perform more complex
> queries.
>
> I noticed that the where() method has no ability to grok ORDER BY
> criteria, so I wrote a patch to that effect:
>
>
http://aaronland.info/perl/sql/abstract/sql-abstract-_recurse_where-
> order-by.diff
>
> I'd also like to be able to define LIKE-like conditions but that will
> ave to wait a day or two. I haven't looked really carefully to see if
> there is a reason why it's not already being done :-)
--- ./SQL-Abstract-1.10/Abstract.pm Fri Sep 27 14:06:32 2002
+++ /usr/local/lib/perl5/site_perl/5.8.0/SQL/Abstract.pm Mon Feb 10 18:28:42 2003
@@ -264,8 +264,7 @@
# order by?
if ($order) {
- my $o = (ref $order eq 'ARRAY') ? join ', ', @$order : $order;
- $sql .= " $SQL{order} $o";
+ $sql .= $self->_order_by($order)
}
return wantarray ? ($sql, @sqlv) : $sql;
@@ -309,13 +308,14 @@
sub where {
my $self = shift;
my $where = shift;
+ my $order = shift;
# precatch for literal string
return $where unless ref $where;
# need a separate routine to properly wrap w/ "where"
my $join = ref $where eq 'ARRAY' ? $SQL{or} : $SQL{and};
- my @ret = $self->_recurse_where($where, $join);
+ my @ret = $self->_recurse_where($where,$join,$order);
return unless @ret;
my $sql = shift @ret;
@@ -328,6 +328,7 @@
my $self = shift;
my $where = shift;
my $join = shift || $SQL{and};
+ my $order = shift;
my $wsql = '';
my(@sqlf, @sqlv) = ();
@@ -351,7 +352,7 @@
}
elsif (ref $where eq 'HASH') {
while (my($k,$v) = each %$where) {
- if (! defined($v)) {
+ if (! defined($v)) {
# undef = null
push @sqlf, "$k $SQL{null}";
} elsif (ref $v eq 'ARRAY') {
@@ -383,7 +384,18 @@
}
$wsql = '( ' . join(" $join ", @sqlf) . ' )';
+
+ # order by?
+ if ($order) {
+ $wsql .= $self->_order_by($order)
+ }
+
return wantarray ? ($wsql, @sqlv) : $wsql;
+}
+
+sub _order_by {
+ my $self = shift;
+ return " $SQL{order} ".((ref $_[0] eq 'ARRAY') ? join(",",@{$_[0]}) : $_[0]);
}
=head1 WHERE CLAUSES
