1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

# 言語: perl
# バージョン: 5.36以上
# 依存: Moo

package QueryBuilder;
use v5.36;
use Moo;

has _table       => (is => 'rw');
has _columns     => (is => 'rw', default => sub { [] });
has _joins       => (is => 'rw', default => sub { [] });
has _conditions  => (is => 'rw', default => sub { [] });
has _group_by    => (is => 'rw', default => sub { [] });
has _having      => (is => 'rw', default => sub { [] });
has _orders      => (is => 'rw', default => sub { [] });
has _limit       => (is => 'rw');
has _offset      => (is => 'rw');
has _bind_values => (is => 'rw', default => sub { [] });

sub from ($self, $table) {
    $self->_table($table);
    return $self;
}

sub select ($self, @columns) {
    push $self->_columns->@*, @columns;
    return $self;
}

# JOIN句
sub join ($self, $table, $on_left, $on_right, $type = 'INNER') {
    push $self->_joins->@*, {
        type     => $type,
        table    => $table,
        on_left  => $on_left,
        on_right => $on_right,
    };
    return $self;
}

sub left_join ($self, $table, $on_left, $on_right) {
    return $self->join($table, $on_left, $on_right, 'LEFT');
}

sub where ($self, $column, $value) {
    push $self->_conditions->@*, { column => $column };
    push $self->_bind_values->@*, $value;
    return $self;
}

# GROUP BY
sub group_by ($self, @columns) {
    push $self->_group_by->@*, @columns;
    return $self;
}

# HAVING
sub having ($self, $column, $op, $value) {
    push $self->_having->@*, { column => $column, op => $op };
    push $self->_bind_values->@*, $value;
    return $self;
}

sub order_by ($self, $column, $dir = 'ASC') {
    push $self->_orders->@*, { column => $column, dir => $dir };
    return $self;
}

sub limit ($self, $count) {
    $self->_limit($count);
    return $self;
}

sub offset ($self, $count) {
    $self->_offset($count);
    return $self;
}

sub build ($self) {
    my @columns = $self->_columns->@*;
    my $cols = @columns ? CORE::join(', ', @columns) : '*';
    
    my $sql = "SELECT $cols FROM " . $self->_table;
    
    # JOIN
    for my $j ($self->_joins->@*) {
        $sql .= " $j->{type} JOIN $j->{table} ON $j->{on_left} = $j->{on_right}";
    }
    
    # WHERE
    if ($self->_conditions->@*) {
        my @wheres = map { "$_->{column} = ?" } $self->_conditions->@*;
        $sql .= " WHERE " . CORE::join(' AND ', @wheres);
    }
    
    # GROUP BY
    if ($self->_group_by->@*) {
        $sql .= " GROUP BY " . CORE::join(', ', $self->_group_by->@*);
    }
    
    # HAVING
    if ($self->_having->@*) {
        my @havings = map { "$_->{column} $_->{op} ?" } $self->_having->@*;
        $sql .= " HAVING " . CORE::join(' AND ', @havings);
    }
    
    # ORDER BY
    if ($self->_orders->@*) {
        my @orders = map { "$_->{column} $_->{dir}" } $self->_orders->@*;
        $sql .= " ORDER BY " . CORE::join(', ', @orders);
    }
    
    # LIMIT / OFFSET
    if ($self->_limit) {
        $sql .= " LIMIT " . $self->_limit;
        if ($self->_offset) {
            $sql .= " OFFSET " . $self->_offset;
        }
    }
    
    return $sql;
}

sub bind_values ($self) {
    return $self->_bind_values->@*;
}

1;

1
2
3
4
5
6
7
8

my $builder = QueryBuilder->new
    ->select('users.name', 'orders.total')
    ->from('users')
    ->join('orders', 'users.id', 'orders.user_id')
    ->where('orders.status', 'completed')
    ->order_by('orders.total', 'DESC');

say $builder->build;

1

SELECT users.name, orders.total FROM users INNER JOIN orders ON users.id = orders.user_id WHERE orders.status = ? ORDER BY orders.total DESC

1
2
3
4
5
6
7
8

my $builder = QueryBuilder->new
    ->select('user_id', 'COUNT(*) as order_count', 'SUM(total) as total_amount')
    ->from('orders')
    ->group_by('user_id')
    ->having('SUM(total)', '>', 10000)
    ->order_by('total_amount', 'DESC');

say $builder->build;

1

SELECT user_id, COUNT(*) as order_count, SUM(total) as total_amount FROM orders GROUP BY user_id HAVING SUM(total) > ? ORDER BY total_amount DESC

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

my $page = 3;
my $per_page = 20;

my $builder = QueryBuilder->new
    ->from('products')
    ->where('category', 'electronics')
    ->order_by('created_at', 'DESC')
    ->limit($per_page)
    ->offset(($page - 1) * $per_page);

say $builder->build;

1

SELECT * FROM products WHERE category = ? ORDER BY created_at DESC LIMIT 20 OFFSET 40