| Subject: | There is no way to specify the 'IN()' comparison operator |
There is currently no way to use the IN() comparison operator, with the where() method, to query a list of field values.
I have attached a patch that allows the user to do this with :
my %where = ( foo => { in => [ "bar","moo","cow" ] }
note: this patch also contains changes discussed in bug #2073.
--- ./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 Wed Feb 12 22:59:27 2003
@@ -89,6 +89,7 @@
and => "AND",
or => "OR",
order => "ORDER BY",
+ in => "IN",
);
=head2 new(case => 'lower', cmp => 'like')
@@ -264,8 +265,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;
@@ -295,7 +295,7 @@
return wantarray ? ($sql, @sqlv) : $sql;
}
-=head2 where(\%where)
+=head2 where(\%where,\@order)
This is used just to generate the WHERE clause. For example,
if you have an arbitrary data structure and know what the
@@ -309,18 +309,24 @@
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);
return unless @ret;
my $sql = shift @ret;
$sql = " $SQL{where} " . $sql if $sql;
+ # order by?
+ if ($order) {
+ $sql .= $self->_order_by($order)
+ }
+
return wantarray ? ($sql, @ret) : $sql;
}
@@ -351,7 +357,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') {
@@ -364,10 +370,19 @@
push @sqlv, @ret;
$wsql = '( ' . join(" $join ", @sqlf) . ' )';
} elsif (ref $v eq 'HASH') {
+
# modified operator { '!=', 'completed' }
my($f,$v) = each %$v;
- push @sqlf, "$k $f ?";
- push @sqlv, $v;
+
+ if (($f =~ /^(in)$/i) && (ref($v) eq "ARRAY")) {
+ push @sqlf, "$k $SQL{in} (".join(",",map{"?"} @$v).")";
+ push @sqlv, @$v;
+ }
+
+ else {
+ push @sqlf, "$k $f ?";
+ push @sqlv, $v;
+ }
} elsif (ref $v eq 'SCALAR') {
# literal SQL
push @sqlf, "$k $$v";
@@ -383,9 +398,15 @@
}
$wsql = '( ' . join(" $join ", @sqlf) . ' )';
+
return wantarray ? ($wsql, @sqlv) : $wsql;
}
+sub _order_by {
+ my $self = shift;
+ return " $SQL{order} ".((ref $_[0] eq 'ARRAY') ? join(",",@{$_[0]}) : $_[0]);
+}
+
=head1 WHERE CLAUSES
This module uses a variation on the idea from L<DBIx::Abstract>. It
@@ -439,6 +460,19 @@
$stmt = "WHERE user = ? AND status != ?";
@bind = ('nwiger', 'completed');
+
+If you want to compare a list of fields using in the IN () comparison
+operator, specify a list in a arrayref, inside the hashref:
+
+ my %where = (
+ status => 'completed',
+ reportid => { 'in', [567,2335,2] }
+ );
+
+Which would generate:
+
+ $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
+ @bind = ('completed', '567', '2335', '2');
So far, we've seen how multiple conditions are joined with C<AND>. However,
we can change this by putting the different conditions we want in hashes