PHP项目Laravel withCount等聚合方法

wen PHP项目 2026-08-12 177

本文目录导读：

withCount() 基本用法
其他聚合方法
完整示例
关联别名和分组
高级用法
性能优化建议
实际应用案例
注意事项

在Laravel中，withCount()是一个非常实用的聚合方法，用于统计关联模型的数量,让我详细介绍一下这个方法和相关的聚合功能。

withCount() 基本用法

基础场景

// 假设有 User 和 Post 模型，用户有多个帖子
$users = User::withCount('posts')->get();
// 结果中会自动添加 posts_count 字段
foreach ($users as $user) {
    echo $user->posts_count;
}

多条件统计

// 统计已发布的帖子数量
$users = User::withCount(['posts' => function ($query) {
    $query->where('status', 'published');
}])->get();
// 多关联统计
$users = User::withCount(['posts', 'comments', 'likes'])->get();

其他聚合方法

withSum() - 求和

// 计算用户的订单总金额
$users = User::withSum('orders', 'total_amount')->get();
// 可以用 $user->orders_sum_total_amount 访问
// 带条件
$users = User::withSum(['orders' => function ($query) {
    $query->where('status', 'completed');
}], 'total_amount')->get();

withMax() / withMin() - 最大值/最小值

// 获取用户最高订单金额
$users = User::withMax('orders', 'total_amount')->get();
// 获取用户最低订单金额
$users = User::withMin('orders', 'total_amount')->get();

withAvg() - 平均值

// 获取用户订单的平均金额
$users = User::withAvg('orders', 'total_amount')->get();

完整示例

// 电商系统中的综合统计
class User extends Model
{
    public function orders()
    {
        return $this->hasMany(Order::class);
    }
    public function products()
    {
        return $this->hasMany(Product::class);
    }
}
// 复杂统计查询
$users = User::withCount(['orders' => function ($query) {
    $query->where('status', 'completed');
}])
->withSum(['orders' => function ($query) {
    $query->where('status', 'completed');
}], 'total_amount')
->withAvg('orders', 'total_amount')
->withMax('orders', 'created_at')
->get();
foreach ($users as $user) {
    echo "订单数: " . $user->orders_count;
    echo "总金额: " . $user->orders_sum_total_amount;
    echo "平均金额: " . $user->orders_avg_total_amount;
    echo "最近订单: " . $user->orders_max_created_at;
}

关联别名和分组

自定义别名

$posts = Post::withCount([
    'comments as approved_comments_count' => function ($query) {
        $query->where('approved', true);
    },
    'comments as pending_comments_count' => function ($query) {
        $query->where('approved', false);
    }
])->get();

复合条件统计

$users = User::withCount([
    'posts' => function ($query) {
        $query->where('status', 'published');
    },
    'posts as draft_posts_count' => function ($query) {
        $query->where('status', 'draft');
    },
    'posts as total_posts_count'
])->get();

高级用法

与作用域结合

class Post extends Model
{
    public function scopePublished($query)
    {
        return $query->where('status', 'published');
    }
}
// 控制器中
$users = User::withCount(['posts' => function ($query) {
    $query->published(); // 使用作用域
}])->get();

多层级关联

// 统计用户文章被评论的数量
$users = User::withCount(['posts.comments as posts_comments_count'])->get();
// 或更复杂
$users = User::withCount([
    'posts' => function ($query) {
        $query->whereHas('comments', function ($q) {
            $q->where('approved', true);
        });
    }
])->get();

子查询聚合

// 使用子查询计算自定义统计
$users = User::select('*')
    ->selectSub(function ($query) {
        $query->from('orders')
            ->whereColumn('orders.user_id', 'users.id')
            ->where('status', 'completed')
            ->selectRaw('SUM(total_amount)');
    }, 'completed_orders_total')
    ->get();

性能优化建议

索引优化

// 确保外键字段有索引
Schema::table('posts', function ($table) {
    $table->index('user_id');
});
// 对统计的字段添加索引
Schema::table('orders', function ($table) {
    $table->index('status');
    $table->index('total_amount');
});

限制查询数量

// 只统计必要的记录
$users = User::withCount(['posts'])
    ->where('active', true)
    ->limit(100)
    ->get();

使用缓存

// 对频繁统计的数据进行缓存
$users = Cache::remember('users_with_post_count', 3600, function () {
    return User::withCount('posts')->get();
});

实际应用案例

博客系统

// 文章列表页展示统计
$posts = Post::withCount(['comments', 'likes', 'views'])
    ->withSum('likes', 'count')
    ->with(['author:id,name'])
    ->latest()
    ->paginate(10);
foreach ($posts as $post) {
    echo "标题: " . $post->title;
    echo "评论数: " . $post->comments_count;
    echo "点赞数: " . $post->likes_count;
    echo "浏览数: " . $post->views_count;
}

数据分析

// 用户行为分析
$users = User::withCount([
    'login_logs as total_logins',
    'orders as total_orders',
    'orders as completed_orders' => function ($query) {
        $query->where('status', 'completed');
    }
])
->withSum('orders', 'grand_total')
->withAvg('orders', 'grand_total')
->get();
// 筛选高价值用户
$vipUsers = User::withSum('orders', 'grand_total')
    ->having('orders_sum_grand_total', '>', 10000)
    ->get();