ajkessel has asked for the wisdom of the Perl Monks concerning the following question:

I often find myself wanting to do an SQL SELECT or UPDATE WHERE clause. I have a consistent awkward way of doing it and I'm wondering if there's something more elegant. For example, let's say I want to set SQL field Y to 0 where SQL field X matches any value in perl array ARRAY. Here's what I do now (with DBIx::Abstract): 
foreach (@ARRAY) {
   push @WHERE, { X => $_ }, "OR";
}
pop @WHERE;
$DB->update(
   { table => 'table',
     fields => { Y => 0 },
     where => \@where
   } );

[download]
It's the "pop" step right after the foreach loop that always seems wrong. If I don't do that "pop," there will be an extra "OR" hanging off at the end. It just seems ugly, though, to be pushing something onto an array just to pop it off after the last iteration. It strikes me as such a common situation that there must be a more elegant construct. What am I missing?

Replies are listed 'Best First'.
Re: Elegant way to turn array of values into SQL "where" clause
by jasonk (Parson) on Jul 03, 2006 at 00:16 UTC

    Try SQL::Abstract instead, then you won't have to construct that awkward array at all...

    use DBI;
use SQL::Abstract;

my $dbh = DBI->connect("dbi:....");
my $sql = SQL::Abstract->new();

my ( $stmt, @bind ) = $sql->update(
    'table',
    { Y => 0 },
    { X => \@ARRAY },
);
my $sth = $dbh->prepare( $stmt );
$sth->execute( @bind );

    [download]
    We're not surrounded, we're in a target-rich environment!
[reply]
[d/l]
Re: Elegant way to turn array of values into SQL "where" clause
by graff (Chancellor) on Jul 02, 2006 at 23:34 UTC
    For a situation like this, I'd probably try the "IN (...)" expression in SQL. I haven't tried DBIx::Abstract, so here's what I would do with straight DBI calls to create, prepare and execute the a suitable SQL statement, using placeholders for the array values: 
    my $sql = "update table set Y=0 where X in (" .
            join( ',', map { '?' } @ARRAY ) .
            ")";

my $sth = $dbh->prepare( $sql );

$sth->execute( @ARRAY );

    [download]
    I'm clueless as to whether DBIx::Abstract provides a means for doing this sort of statement construction. If not, that's sad, but you should just be able to fall back on straight DBI calls whenever you want/need them.
[reply]
[d/l]
Re: Elegant way to turn array of values into SQL "where" clause
by GrandFather (Saint) on Jul 02, 2006 at 22:37 UTC

    Not sure if you consider it more elegant, but you could:

    my @where = (@array[0]);
push @where, 'OR', {x => $_} for @array[1..$#array];

    [download]
    DWIM is Perl's answer to Gödel
[reply]
[d/l]
Re: Elegant way to turn array of values into SQL "where" clause
by Hue-Bond (Priest) on Jul 02, 2006 at 22:41 UTC

    What about:

    $WHERE = join ' OR ', map { X => $_ }, @ARRAY;
$DB->update(
   { table => 'table',
     fields => { Y => 0 },
     where => $WHERE
   } );

    [download]

    Update: Bugs always appear *after* posting :). [...] I misunderstood DBIx::Abstract documentation. Here's my next attempt. It's a bit more complicated though. Fill @WHERE with enough 'OR's and then place the hashrefs over the even elements:

    my @ARRAY=qw/foo bar baz/;
my @WHERE=('OR') x (2*@ARRAY - 1);
for (0 .. $#ARRAY) {
  $WHERE[2*$_] = { X => $ARRAY[$_] };
}

    [download]

    --
    David Serrano

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom