more elegant way to construct SQL adding WHERE and using placeholders

Question

What is the best way to construct sql with various number of WHERE conditions ? My solution looks ugly:

my ($where, @values);
if ($phone_number)
{
    $where = 'AND pnone_number=?';
    @values = ($from, $till, $phone_number);
}
else 
{
    $where = '';
    @values = ($from, $till);
}
my $sql = 'SELECT * FROM calls WHERE time between ? AND ? '.$where.' ORDER BY time';
my $res = $dbh->selectall_arrayref($sql, undef, @values) or warn 'error';

SQL::Maker would abstract away the SQL details.

DavidO
– DavidO

2013-05-20 08:14:04 +00:00
Commented May 20, 2013 at 8:14 — DavidO
– DavidO, Commented May 20, 2013 at 8:14

friedo · Accepted Answer · 2013-05-20 08:18:58Z

10

How about:

my $where = '';
my @values = ( $from, $till );

if ( $phone_number ) { 
    $where = 'AND phone_number=?';
    push @values, $phone_number;
}

That eliminates the need for your else clause.

You could also use something like SQL::Abstract.

use SQL::Abstract;

...

my ( $sql, @values ) = SQL::Abstract->new->select(
    'calls',                                                    # table
    '*',                                                        # columns
    { time => { '<=' => $till, '>' => $from },                  # where clause
      $phone_number ? ( phone_number => $phone_number ) : ( ),
    },
    'time'                                                      # order clause
);

answered May 20, 2013 at 8:18

friedo

67.3k17 gold badges119 silver badges186 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

daxim Over a year ago

DBIx::Class uses SQL::Abstract under the hood.

$schema->resultset('Calls')->search({ time => {-between, [$from, $until]}, defined $phone ? (phone => $phone) : () }, { order_by => {-asc => 'time'} } )->all

mpapec · Accepted Answer · 2013-05-20 11:09:15Z

1

1=1 is added for cases when $where would be epmty.

my $where = "AND time between ? AND ? ";
my @values = ($from, $till);

if ($phone_number) {
    $where .= 'AND pnone_number=? ';
    push @values, $phone_number;
}

my $sql = 'SELECT * FROM calls WHERE 1=1 $where ORDER BY time';
my $res = $dbh->selectall_arrayref($sql, undef, @values) or warn 'error';

edited May 20, 2013 at 11:09

answered May 20, 2013 at 9:24

mpapec

50.7k8 gold badges72 silver badges133 bronze badges

Comments

Zaid · Accepted Answer · 2013-05-20 11:30:21Z

0

Conditional list-include (aka "enterprise"):

my @values = ( $from,
               $till,
               ( $phone_number ) x !! $phone_number,
             );

my $sql = 'SELECT * FROM calls WHERE time between ? AND ? '
        . 'AND phone_number=?' x !! $phone_number
        . ' ORDER BY time';

answered May 20, 2013 at 11:30

Zaid

37.3k16 gold badges89 silver badges160 bronze badges

2 Comments

mpapec Over a year ago

"enterprise" may look good for golfing, but I wouldn't use it otherwise.

friedo Over a year ago

These are the voyages of the Perlship Enterprise, discovering new operators and new syntactations....

Collectives™ on Stack Overflow

more elegant way to construct SQL adding WHERE and using placeholders

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related