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

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

package QueryDirector;
use v5.36;
use Moo;
use QueryBuilder;

# ページネーション付き検索
sub build_paginated_search ($self, %opts) {
    my $table    = $opts{table};
    my $filters  = $opts{filters} // {};
    my $order_by = $opts{order_by} // 'id';
    my $order    = $opts{order} // 'ASC';
    my $page     = $opts{page} // 1;
    my $per_page = $opts{per_page} // 20;
    
    my $builder = QueryBuilder->new->from($table);
    
    for my $column (keys $filters->%*) {
        $builder->where($column, $filters->{$column});
    }
    
    $builder->order_by($order_by, $order)
            ->limit($per_page)
            ->offset(($page - 1) * $per_page);
    
    return $builder;
}

# ユーザー別集計レポート
sub build_user_aggregate ($self, %opts) {
    my $table      = $opts{table};
    my $sum_column = $opts{sum_column};
    my $min_total  = $opts{min_total} // 0;
    
    my $builder = QueryBuilder->new
        ->select('user_id', "COUNT(*) as count", "SUM($sum_column) as total")
        ->from($table)
        ->group_by('user_id');
    
    if ($min_total > 0) {
        $builder->having("SUM($sum_column)", '>', $min_total);
    }
    
    $builder->order_by('total', 'DESC');
    
    return $builder;
}

# ユーザーと関連注文を取得
sub build_user_with_orders ($self, %opts) {
    my $user_id = $opts{user_id};
    my $status  = $opts{status};
    
    my $builder = QueryBuilder->new
        ->select('users.id', 'users.name', 'orders.id as order_id', 'orders.total')
        ->from('users')
        ->left_join('orders', 'users.id', 'orders.user_id')
        ->where('users.id', $user_id);
    
    if ($status) {
        $builder->where('orders.status', $status);
    }
    
    $builder->order_by('orders.created_at', 'DESC');
    
    return $builder;
}

# 最近のアクティブユーザー
sub build_recent_active_users ($self, %opts) {
    my $days  = $opts{days} // 7;
    my $limit = $opts{limit} // 100;
    
    return QueryBuilder->new
        ->select('users.*', 'COUNT(orders.id) as order_count')
        ->from('users')
        ->join('orders', 'users.id', 'orders.user_id')
        ->group_by('users.id')
        ->having('COUNT(orders.id)', '>', 0)
        ->order_by('order_count', 'DESC')
        ->limit($limit);
}

1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

use QueryDirector;

my $director = QueryDirector->new;

# 3ページ目のアクティブユーザーを取得
my $builder = $director->build_paginated_search(
    table    => 'users',
    filters  => { status => 'active', role => 'member' },
    order_by => 'created_at',
    order    => 'DESC',
    page     => 3,
    per_page => 25,
);

say $builder->build;
# SELECT * FROM users WHERE status = ? AND role = ? ORDER BY created_at DESC LIMIT 25 OFFSET 50

1
2
3
4
5
6
7
8
9

# 購入金額が10000円以上のユーザーを集計
my $builder = $director->build_user_aggregate(
    table      => 'orders',
    sum_column => 'total',
    min_total  => 10000,
);

say $builder->build;
# SELECT user_id, COUNT(*) as count, SUM(total) as total FROM orders GROUP BY user_id HAVING SUM(total) > ? ORDER BY total DESC

1
2
3
4
5
6
7
8

# 特定ユーザーの完了済み注文を取得
my $builder = $director->build_user_with_orders(
    user_id => 42,
    status  => 'completed',
);

say $builder->build;
# SELECT users.id, users.name, orders.id as order_id, orders.total FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE users.id = ? AND orders.status = ? ORDER BY orders.created_at DESC