PHP项目ThinkPHP关联统计与聚合

本文目录导读：

1. 关联统计
2. 关联聚合
3. 多级关联统计
4. 原生SQL聚合
5. 条件聚合与统计
6. 性能优化技巧
7. 完整实例：商城订单统计
8. 常见问题与解决方案

在ThinkPHP框架中,关联统计与聚合是处理模型间复杂数据关系的强大功能，下面从基础到进阶详细说明。

关联统计

基础关联统计（hasMany）

// 定义模型
class User extends Model
{
    // 用户关联多个评论
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
    // 用户关联多个订单
    public function orders()
    {
        return $this->hasMany(Order::class);
    }
}
// 使用关联统计
$users = User::withCount('comments')->select();
foreach ($users as $user) {
    echo $user->comments_count; // 输出评论数量
}

多条件关联统计

// 按状态统计
$users = User::withCount([
    'comments',
    'orders' => function ($query) {
        $query->where('status', 1); // 只统计已完成订单
    }
])->select();
// 输出
echo $users[0]->comments_count;
echo $users[0]->orders_count;

关联统计排序

// 按评论数量排序
$hotUsers = User::withCount('comments')
    ->orderBy('comments_count', 'desc')
    ->limit(10)
    ->select();

关联聚合

关联求和（sum）

class Order extends Model
{
    public function items()
    {
        return $this->hasMany(OrderItem::class);
    }
}
// 统计订单总金额
$orders = Order::withSum('items', 'price')->select();
foreach ($orders as $order) {
    echo $order->items_sum_price;
}
// 带条件求和多表字段
$orders = Order::withSum([
    'items' => function ($query) {
        $query->where('status', 'valid');
    }
], 'price')->select();

其他聚合操作

// 关联最大值
$users = User::withMax('orders', 'total_amount')
    ->withMin('orders', 'total_amount')
    ->withAvg('orders', 'total_amount')
    ->select();
// 访问结果
$user->orders_max_total_amount;
$user->orders_min_total_amount;
$user->orders_avg_total_amount;

关联计数（distinct）

// 统计不重复的用户ID
$orders = Order::withCount(['users' => function ($query) {
    $query->distinct();
}])->select();

多级关联统计

class Category extends Model
{
    // 分类关联文章
    public function articles()
    {
        return $this->hasMany(Article::class);
    }
}
class Article extends Model
{
    // 文章关联评论
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
    // 文章关联点赞
    public function likes()
    {
        return $this->hasMany(Like::class);
    }
}
// 多级关联统计
$categories = Category::withCount([
    'articles',
    'articles as hot_articles_count' => function ($query) {
        $query->where('views', '>', 1000);
    },
    'articles.comments as articles_comments_count',
    'articles.likes as articles_likes_count'
])->select();
// 使用
foreach ($categories as $category) {
    echo $category->articles_count;
    echo $category->hot_articles_count;
    echo $category->articles_comments_count;
    echo $category->articles_likes_count;
}

原生SQL聚合

// 使用聚合函数
$stats = User::select('id', 'name')
    ->withCount('orders as order_count')
    ->withSum('orders as total_amount', 'amount')
    ->withAggregate('orders as max_order_amount', 'max(amount)')
    ->get();
// 更复杂的原生查询
$result = Db::name('orders')
    ->join('users', 'orders.user_id = users.id')
    ->field('users.id, users.name, COUNT(orders.id) as order_count, SUM(orders.amount) as total_amount')
    ->group('users.id')
    ->having('order_count > ' . 5)
    ->select();

条件聚合与统计

// 条件聚合
$users = User::withCount([
    'orders as pending_orders' => function ($query) {
        $query->where('status', 'pending');
    },
    'orders as completed_orders' => function ($query) {
        $query->where('status', 'completed');
    },
    'orders as total_order_count'
])->select();
// 日期范围统计
$monthlyStats = User::withCount([
    'orders as january_orders' => function ($query) {
        $query->whereMonth('created_at', '01');
    },
    'orders as february_orders' => function ($query) {
        $query->whereMonth('created_at', '02');
    }
])->select();

性能优化技巧

避免N+1查询

// 错误示例（会产生N+1查询）
$users = User::all();
foreach ($users as $user) {
    $count = $user->comments()->count();
}
// 正确示例（预加载）
$users = User::withCount('comments')->select();
// 或者预处理
$users = User::with(['comments'])->select();

使用select限制字段

// 只查询需要的字段
$users = User::select('id', 'name')
    ->withCount('comments')
    ->withSum('orders', 'amount')
    ->get();

分页处理大数据量

// 分页统计
$result = User::withCount('comments')
    ->paginate(20); // 每页20条

完整实例：商城订单统计

// 订单控制器
class OrderController extends Controller
{
    // 统计今日订单
    public function todayStats()
    {
        $today = date('Y-m-d');
        $stats = Order::whereDate('created_at', $today)
            ->withCount('items as item_total')
            ->withSum('items', 'price as total_amount')
            ->first();
        return json($stats);
    }
    // 用户订单统计
    public function userOrderStats($userId)
    {
        $user = User::withCount([
            'orders as total_orders',
            'orders as completed_orders' => function ($query) {
                $query->where('status', 'completed');
            },
            'orders as pending_orders' => function ($query) {
                $query->where('status', 'pending');
            }
        ])
        ->withSum('orders as total_spent', 'amount')
        ->find($userId);
        return json([
            'total_orders' => $user->total_orders,
            'completed_orders' => $user->completed_orders,
            'pending_orders' => $user->pending_orders,
            'total_spent' => $user->total_spent,
        ]);
    }
}

常见问题与解决方案

聚合字段重名

// 使用别名避免冲突
$users = User::withCount([
    'orders as order_count',
    'comments as comment_count'
])->select();

多字段聚合

// 多个字段求和
$users = User::withSum('orders as amount_sum', 'amount')
    ->withSum('orders as discount_sum', 'discount')
    ->select();

聚合结果排序

// 按聚合结果排序
$topUsers = User::withSum('orders as total_amount', 'amount')
    ->orderBy('total_amount', 'desc')
    ->take(10)
    ->select();

分组统计

// 按日期分组统计
$dailyStats = Order::selectRaw('DATE(created_at) as date, COUNT(*) as order_count, SUM(amount) as total_amount')
    ->groupBy('date')
    ->orderBy('date', 'desc')
    ->get();

这些关联统计与聚合功能让ThinkPHP在处理复杂数据关系时更加高效和优雅,合理使用这些功能可以大幅提升开发效率和查询性能。