Efficient Aggregation of Related Models with Eloquent `withCount` and `withSum`

use App\Models\User;
use App\Models\Post;
use App\Models\Order;

// Example 1: Get user with their posts count
// Assume a User hasMany Posts
$usersWithPostCounts = User::withCount('posts')->get();

echo "Users with Post Counts:
";
foreach ($usersWithPostCounts as $user) {
    echo "User: {$user->name}, Posts Count: {$user->posts_count}
";
}

// Example 2: Get posts with comments count and also total 'likes' (if comments had a 'likes' column)
// For demonstration, let's assume Post hasMany Comments and Comment has a 'rating' integer column
// and we want to sum the ratings of approved comments.
// (This requires a 'rating' column on comments table in real scenario)
$postsWithAggregations = Post::withCount('comments')
    ->withSum(['comments as approved_comments_total_rating' => function ($query) {
        $query->where('approved', true);
    }], 'rating')
    ->get();

echo "
Posts with Comments Count and Approved Comments Total Rating:
";
foreach ($postsWithAggregations as $post) {
    echo "Post: {$post->title}
";
    echo "  - Total Comments: {$post->comments_count}
";
    echo "  - Approved Comments Total Rating: {$post->approved_comments_total_rating}
";
}

// Example 3: Count users with at least one published post
// This uses a conditional aggregation within the main query, but applies to the parent.
// Let's assume a User hasMany Orders, and Order has a 'amount' column.
// Get users with total amount of 'completed' orders.
$usersWithCompletedOrderAmounts = User::withSum(['orders as completed_orders_amount' => function ($query) {
    $query->where('status', 'completed');
}], 'amount')->get();

echo "
Users with Total Completed Order Amounts:
";
foreach ($usersWithCompletedOrderAmounts as $user) {
    echo "User: {$user->name}, Completed Orders Amount: {$user->completed_orders_amount}
";
}

// Database migration examples:
// Schema::create('users', function (Blueprint $table) { $table->id(); $table->string('name'); ... });
// Schema::create('posts', function (Blueprint $table) { $table->id(); $table->foreignId('user_id'); ... });
// Schema::create('comments', function (Blueprint $table) { $table->id(); $table->morphs('commentable'); $table->integer('rating')->default(0); $table->boolean('approved')->default(false); ... });
// Schema::create('orders', function (Blueprint $table) { $table->id(); $table->foreignId('user_id'); $table->decimal('amount', 8, 2); $table->string('status'); ... });