File Coverage

blib/lib/SQL/Maker/Select.pm

Criterion	Covered	Total	%
statement	205	210	97.6
branch	87	98	88.7
condition	29	49	59.1
subroutine	33	33	100.0
pod	14	23	60.8
total	368	413	89.1

use strict;
use warnings;
use utf8;
use SQL::Maker::Condition;
use SQL::Maker::Util;
use Class::Accessor::Lite (
    new => 0,
    wo => [qw/distinct for_update/],
    rw => [qw/prefix/],
    ro => [qw/quote_char name_sep new_line strict/],
);
use Scalar::Util ();

    if (@_==1) {
        return $_[0]->{offset};
    } else {
        $_[0]->{offset} = $_[1];
        return $_[0];
    }
}

    if (@_==1) {
        $_[0]->{limit};
    } else {
        $_[0]->{limit} = $_[1];
        return $_[0];
    }
}

    my $class = shift;
    my %args = @_ == 1 ? %{$_[0]} : @_;
    my $self = bless {
        select             => +[],
        distinct           => 0,
        select_map         => +{},
        select_map_reverse => +{},
        from               => +[],
        joins              => +[],
        index_hint         => +{},
        group_by           => +[],
        order_by           => +[],
        prefix             => 'SELECT ',
        new_line           => "\n",
        strict             => 0,
        %args
    }, $class;

    return $self;
}

    my $self = shift;

    SQL::Maker::Condition->new(
        quote_char => $self->{quote_char},
        name_sep   => $self->{name_sep},
        strict     => $self->{strict},
    );
}

    my $self = shift;
    my @bind;
    push @bind, @{$self->{subqueries}} if $self->{subqueries};
    push @bind, $self->{where}->bind  if $self->{where};
    push @bind, $self->{having}->bind if $self->{having};
    return wantarray ? @bind : \@bind;
}

    my ($self, $term, $col) = @_;

    push @{ $self->{select} }, $term;
    if ($col) {
        $col ||= $term;
        $self->{select_map_reverse}->{$col} = $term;
    }
    return $self;
}

    my ($self, $table, $alias) = @_;
    if ( Scalar::Util::blessed( $table ) and $table->can('as_sql') ) {
        push @{ $self->{subqueries} }, $table->bind;
        push @{$self->{from}}, [ \do{ '(' . $table->as_sql . ')' }, $alias ];
    }
    else {
        push @{$self->{from}}, [$table, $alias];
    }
    return $self;
}

    my ($self, $table_ref, $joins) = @_;
    my ($table, $alias) = ref($table_ref) eq 'ARRAY' ? @$table_ref : ($table_ref);

    if ( Scalar::Util::blessed( $table ) and $table->can('as_sql') ) {
        push @{ $self->{subqueries} }, $table->bind;
        $table = \do{ '(' . $table->as_sql . ')' };
    }

    if ( Scalar::Util::blessed( $joins->{table} ) and $joins->{table}->can('as_sql') ) {
        push @{ $self->{subqueries} }, $joins->{table}->bind;
        $joins->{table} = \do{ '(' . $joins->{table}->as_sql . ')' };
    }

    push @{ $self->{joins} }, {
        table => [ $table, $alias ],
        joins => $joins,
    };
    return $self;
}

    my ($self, $table, $hint) = @_;

    my ($type, $list);

    if (ref $hint eq 'HASH') {
        # { type => '...', list => ['foo'] }
        $type = $hint->{type} || 'USE';
        $list = ref($hint->{list}) eq 'ARRAY' ? $hint->{list} : [ $hint->{list} ];
    } else {
        # ['foo, 'bar'] or just 'foo'
        $type = 'USE';
        $list = ref($hint) eq 'ARRAY' ? $hint : [ $hint ];
    }

    $self->{index_hint}->{$table} = {
        type => $type,
        list => $list,
    };

    return $self;
}

    my ($self, $label) = @_;

    return $$label if ref $label eq 'SCALAR';
    SQL::Maker::Util::quote_identifier($label, $self->{quote_char}, $self->{name_sep})
}

    my $self = shift;
    my $sql = '';
    my $new_line = $self->new_line;

    if (@{ $self->{select} }) {
        $self->{select_map} = { reverse %{$self->{select_map_reverse}} };
        $sql .= $self->{prefix};
        $sql .= 'DISTINCT ' if $self->{distinct};
        $sql .= join(', ',  map {
            my $alias = $self->{select_map}->{$_};
            if (!$alias) {
                $self->_quote($_)
            } elsif ($alias && $_ =~ /(?:^|\.)\Q$alias\E$/) {
                $self->_quote($_)
            } else {
                $self->_quote($_) . ' AS ' .  $self->_quote($alias)
            }
        } @{ $self->{select} }) . $new_line;
    }

    $sql .= 'FROM ';

    ## Add any explicit JOIN statements before the non-joined tables.
    if ($self->{joins} && @{ $self->{joins} }) {
        my $initial_table_written = 0;
        for my $j (@{ $self->{joins} }) {
            my ($table, $join) = map { $j->{$_} } qw( table joins );
            $table = $self->_add_index_hint(@$table); ## index hint handling
            $sql .= $table unless $initial_table_written++;
            $sql .= ' ' . uc($join->{type}) if $join->{type};
            $sql .= ' JOIN ' . $self->_quote($join->{table});
            $sql .= ' ' . $self->_quote($join->{alias}) if $join->{alias};

            if ( defined $join->{condition} ) {
                if (ref $join->{condition} && ref $join->{condition} eq 'ARRAY') {
                    $sql .= ' USING ('. join(', ', map { $self->_quote($_) } @{ $join->{condition} }) . ')';
                }
                elsif (ref $join->{condition} && ref $join->{condition} eq 'HASH') {
                    my @conds;
                    for my $key (keys %{ $join->{condition} }) {
                        push @conds, $self->_quote($key) . ' = ' . $self->_quote($join->{condition}{$key});
                    }
                    $sql .= ' ON ' . join(' AND ', @conds);
                }
                else {
                    $sql .= ' ON ' . $join->{condition};
                }
            }
        }
        $sql .= ', ' if @{ $self->{from} };
    }

    if ($self->{from} && @{ $self->{from} }) {
        $sql .= join ', ',
          map { $self->_add_index_hint($_->[0], $_->[1]) }
             @{ $self->{from} };
    }

    $sql .= $new_line;
    $sql .= $self->as_sql_where()   if $self->{where};

    $sql .= $self->as_sql_group_by  if $self->{group_by};
    $sql .= $self->as_sql_having    if $self->{having};
    $sql .= $self->as_sql_order_by  if $self->{order_by};

    $sql .= $self->as_sql_limit     if defined $self->{limit};

    $sql .= $self->as_sql_for_update;
    $sql =~ s/${new_line}+$//;

    return $sql;
}

    my $self = shift;

    my $n = $self->{limit};
    return '' unless defined $n;

    die "Non-numerics in limit clause ($n)" if $n =~ /\D/;
    return sprintf "LIMIT %d%s" . $self->new_line, $n,
           ($self->{offset} ? " OFFSET " . int($self->{offset}) : "");
}

    my ($self, $col, $type) = @_;
    push @{$self->{order_by}}, [$col, $type];
    return $self;
}

    my ($self) = @_;

    my @attrs = @{$self->{order_by}};
    return '' unless @attrs;

    return 'ORDER BY '
           . join(', ', map {
                my ($col, $type) = @$_;
                if (ref $col) {
                    $$col
                } else {
                    $type ? $self->_quote($col) . " $type" : $self->_quote($col)
                }
           } @attrs)
           . $self->new_line;
}

    my ($self, $group, $order) = @_;
    push @{$self->{group_by}}, $order ? $self->_quote($group) . " $order" : $self->_quote($group);
    return $self;
}

    my ($self,) = @_;

    my $elems = $self->{group_by};

    return '' if @$elems == 0;

    return 'GROUP BY '
           . join(', ', @$elems)
           . $self->new_line;
}

    my ($self, $where) = @_;
    $self->{where} = $where;
    return $self;
}

    my ($self, $col, $val) = @_;

    $self->{where} ||= $self->new_condition();
    $self->{where}->add($col, $val);
    return $self;
}

    my ($self, $term, $bind) = @_;

    $self->{where} ||= $self->new_condition();
    $self->{where}->add_raw($term, $bind);
    return $self;
}

    my $self = shift;

    my $where = $self->{where}->as_sql(undef, sub { $self->_quote($_[0]) });
    $where ? "WHERE $where" . $self->new_line : '';
}

    my $self = shift;
    if ($self->{having}) {
        'HAVING ' . $self->{having}->as_sql . $self->new_line;
    } else {
        ''
    }
}

    my ($self, $col, $val) = @_;

    if (my $orig = $self->{select_map_reverse}->{$col}) {
        $col = $orig;
    }

    $self->{having} ||= $self->new_condition();
    $self->{having}->add($col, $val);
    return $self;
}

    my ($self, $term, $bind) = @_;

    $self->{having} ||= $self->new_condition();
    $self->{having}->add_raw($term, $bind);
    return $self;
}

    my $self = shift;
    $self->{for_update} ? ' FOR UPDATE' : '';
}

    my ($self, $tbl_name, $alias) = @_;
    my $quoted = $alias ? $self->_quote($tbl_name) . ' ' . $self->_quote($alias) : $self->_quote($tbl_name);
    my $hint = $self->{index_hint}->{$tbl_name};
    return $quoted unless $hint && ref($hint) eq 'HASH';
    if ($hint->{list} && @{ $hint->{list} }) {
        return $quoted . ' ' . uc($hint->{type} || 'USE') . ' INDEX (' .
                join (',', map { $self->_quote($_) } @{ $hint->{list} }) .
                ')';
    }
    return $quoted;
}

1;

=head1 NAME

SQL::Maker::Select - dynamic SQL generator

=head1 SYNOPSIS

    my $sql = SQL::Maker::Select->new()
                                  ->add_select('foo')
                                  ->add_select('bar')
                                  ->add_select('baz')
                                  ->add_from('table_name')
                                  ->as_sql;
    # => "SELECT foo, bar, baz FROM table_name"

=head1 DESCRIPTION

=head1 METHODS

=over 4

=item C<< my $sql = $stmt->as_sql(); >>

Render the SQL string.

=item C<< my @bind = $stmt->bind(); >>

Get the bind variables.

=item C<< $stmt->add_select('*') >>

=item C<< $stmt->add_select($col => $alias) >>

=item C<< $stmt->add_select(\'COUNT(*)' => 'cnt') >>

Add a new select term. It's automatically quoted.

=item C<< $stmt->add_from($table :Str | $select :SQL::Maker::Select) : SQL::Maker::Select >>

Add a new FROM clause. You can specify the table name or an instance of L<SQL::Maker::Select> for a sub-query.

I<Return:> $stmt itself.

=item C<< $stmt->add_join(user => {type => 'inner', table => 'config', condition => 'user.user_id = config.user_id'}); >>

=item C<< $stmt->add_join(user => {type => 'inner', table => 'config', condition => {'user.user_id' => 'config.user_id'}); >>

=item C<< $stmt->add_join(user => {type => 'inner', table => 'config', condition => ['user_id']}); >>

=item C<< $stmt->add_join(user => {type => 'inner', table => $select :SQL::Maker::Select, condition => 'user.user_id = config.user_id'}); >>

Add a new JOIN clause. If you pass an arrayref for 'condition' then it uses 'USING'. If 'type' is omitted
it falls back to plain JOIN.

You can specify the table name or an instance of L<SQL::Maker::Select> for a sub-query in 'table' argument.

    my $stmt = SQL::Maker::Select->new();
    $stmt->add_join(
        user => {
            type      => 'inner',
            table     => 'config',
            condition => 'user.user_id = config.user_id',
        }
    );
    $stmt->as_sql();
    # => 'FROM user INNER JOIN config ON user.user_id = config.user_id'

    my $stmt = SQL::Maker::Select->new(quote_char => '`', name_sep => '.');
    $stmt->add_join(
        user => {
            type      => 'inner',
            table     => 'config',
            condition => {'user.user_id' => 'config.user_id'},
        }
    );
    $stmt->as_sql();
    # => 'FROM `user` INNER JOIN `config` ON `user`.`user_id` = `config`.`user_id`'

    my $stmt = SQL::Maker::Select->new();
    $stmt->add_select('name');
    $stmt->add_join(
        user => {
            type      => 'inner',
            table     => 'config',
            condition => ['user_id'],
        }
    );
    $stmt->as_sql();
    # => 'SELECT name FROM user INNER JOIN config USING (user_id)'

    my $subquery = SQL::Maker::Select->new();
    $subquery->add_select('*');
    $subquery->add_from( 'foo' );
    $subquery->add_where( 'hoge' => 'fuga' );
    my $stmt = SQL::Maker::Select->new();
    $stmt->add_join(
        [ $subquery, 'bar' ] => {
            type      => 'inner',
            table     => 'baz',
            alias     => 'b1',
            condition => 'bar.baz_id = b1.baz_id'
        },
    );
    $stmt->as_sql;
    # => "FROM (SELECT * FROM foo WHERE (hoge = ?)) bar INNER JOIN baz b1 ON bar.baz_id = b1.baz_id";

    my $subquery1 = SQL::Maker::Select->new();
    $subquery1->add_select('*');
    $subquery1->add_from( 'foo' );
    $subquery1->add_where( 'hoge' => 'fuga' );
    my $subquery2 = SQL::Maker::Select->new();
    $subquery2->add_select('*');
    $subquery2->add_from( 'bar' );
    $subquery2->add_where( 'piyo' => 'gera' );
    my $stmt = SQL::Maker::Select->new();
    $stmt->add_join(
        [ $subquery1, 'f1' ] => {
            type      => 'inner',
            table     => $subquery2,
            alias     => 'b1',
            condition => 'f1.baz_id = b1.baz_id'
        },
    );
    $stmt->as_sql;
    # => "FROM (SELECT * FROM foo WHERE (hoge = ?)) f1 INNER JOIN (SELECT * FROM bar WHERE (piyo = ?)) b1 ON f1.baz_id = b1.baz_id";

=item C<< $stmt->add_index_hint(foo => {type => 'USE', list => ['index_hint']}); >>

=item C<< $stmt->add_index_hint(foo => 'index_hint'); >>

=item C<< $stmt->add_index_hint(foo => ['index_hint']); >>

    my $stmt = SQL::Maker::Select->new();
    $stmt->add_select('name');
    $stmt->add_from('user');
    $stmt->add_index_hint(user => {type => 'USE', list => ['index_hint']});
    $stmt->as_sql();
    # => "SELECT name FROM user USE INDEX (index_hint)"

=item C<< $stmt->add_where('foo_id' => 'bar'); >>

Add a new WHERE clause.

    my $stmt = SQL::Maker::Select->new()
                                   ->add_select('c')
                                   ->add_from('foo')
                                   ->add_where('name' => 'john')
                                   ->add_where('type' => {IN => [qw/1 2 3/]})
                                   ->as_sql();
    # => "SELECT c FROM foo WHERE (name = ?) AND (type IN (?, ?, ?))"

=item C<< $stmt->add_where_raw('id = ?', [1]) >>

Add a new WHERE clause from raw placeholder string and bind variables.

    my $stmt = SQL::Maker::Select->new()
                                   ->add_select('c')
                                   ->add_from('foo')
                                   ->add_where_raw('EXISTS(SELECT * FROM bar WHERE name = ?)' => ['john'])
                                   ->add_where_raw('type IS NOT NULL')
                                   ->as_sql();
    # => "SELECT c FROM foo WHERE (EXISTS(SELECT * FROM bar WHERE name = ?)) AND (type IS NOT NULL)"


=item C<< $stmt->set_where($condition) >>

Set the WHERE clause.

$condition should be instance of L<SQL::Maker::Condition>.

    my $cond1 = SQL::Maker::Condition->new()
                                       ->add("name" => "john");
    my $cond2 = SQL::Maker::Condition->new()
                                       ->add("type" => {IN => [qw/1 2 3/]});
    my $stmt = SQL::Maker::Select->new()
                                   ->add_select('c')
                                   ->add_from('foo')
                                   ->set_where($cond1 & $cond2)
                                   ->as_sql();
    # => "SELECT c FROM foo WHERE ((name = ?)) AND ((type IN (?, ?, ?)))"

=item C<< $stmt->add_order_by('foo'); >>

=item C<< $stmt->add_order_by({'foo' => 'DESC'}); >>

Add a new ORDER BY clause.

    my $stmt = SQL::Maker::Select->new()
                                   ->add_select('c')
                                   ->add_from('foo')
                                   ->add_order_by('name' => 'DESC')
                                   ->add_order_by('id')
                                   ->as_sql();
    # => "SELECT c FROM foo ORDER BY name DESC, id"

=item C<< $stmt->add_group_by('foo'); >>

Add a new GROUP BY clause.

    my $stmt = SQL::Maker::Select->new()
                                   ->add_select('c')
                                   ->add_from('foo')
                                   ->add_group_by('id')
                                   ->as_sql();
    # => "SELECT c FROM foo GROUP BY id"

    my $stmt = SQL::Maker::Select->new()
                                   ->add_select('c')
                                   ->add_from('foo')
                                   ->add_group_by('id' => 'DESC')
                                   ->as_sql();
    # => "SELECT c FROM foo GROUP BY id DESC"

=item C<< $stmt->limit(30) >>

=item C<< $stmt->offset(5) >>

Add LIMIT and OFFSET.

    my $stmt = SQL::Maker::Select->new()
                                   ->add_select('c')
                                   ->add_from('foo')
                                   ->limit(30)
                                   ->offset(5)
                                   ->as_sql();
    # => "SELECT c FROM foo LIMIT 30 OFFSET 5"

=item C<< $stmt->add_having(cnt => 2) >>

Add a HAVING clause.

    my $stmt = SQL::Maker::Select->new()
                                   ->add_from('foo')
                                   ->add_select(\'COUNT(*)' => 'cnt')
                                   ->add_having(cnt => 2)
                                   ->as_sql();
    # => "SELECT COUNT(*) AS cnt FROM foo HAVING (COUNT(*) = ?)"

=back

=head1 SEE ALSO

L<Data::ObjectDriver::SQL>