如何实现集合嵌套集合？Laravel用户-项目工时关联查询问题求助

Hey there! Let's break down what's going wrong with your current code and get you the nested data structure you need—users with their associated projects and total hours worked on each.

What's Off With Your Current Code? #

• Duplicate/Incomplete Data: Using join directly creates a flat result set where users repeat for every matching time entry, and you're only selecting IDs and the sum, not full user/project details.
• Missing Grouping: You're using sum(hours) but haven't grouped the results by user and project, so the sum won't calculate correctly per user-project pair.
• No Nested Structure: The output is a flat array, not the nested "user → projects → total hours" structure you're after.

Solution 1: Use Eloquent Relationships (Cleaner Laravel Approach) #

First, make sure your User model has a many-to-many relationship with Project via the time_entries pivot table:

// app/Models/User.php
public function projects()
{
    return $this->belongsToMany(Project::class, 'time_entries')
                ->withPivot('hours', 'spent_on') // Include pivot table fields we need
                ->withTimestamps();
}

Then query users, filter by the date range, load their relevant projects, and calculate total hours:

$debut = $request->input('debut');
$fin = $request->input('fin');

$users = User::with(['projects' => function ($query) use ($debut, $fin) {
    // Filter projects to only those with time entries in the date range
    $query->whereBetween('time_entries.spent_on', [$debut, $fin])
          // Calculate total hours per project for the user
          ->selectRaw('projects.*, SUM(time_entries.hours) as total_hours')
          // Group by project to get the correct sum
          ->groupBy('projects.id');
}])
// Only include users who have at least one matching time entry
->whereHas('projects', function ($query) use ($debut, $fin) {
    $query->whereBetween('time_entries.spent_on', [$debut, $fin]);
})
->get();

Now each $user in your result will have a projects attribute containing the projects they worked on, each with a total_hours field.

Solution 2: Query Builder with Manual Nesting #

If you prefer using the query builder directly, you can first fetch the flat aggregated data, then restructure it into the nested format:

$debut = $request->input('debut');
$fin = $request->input('fin');

// Get aggregated user-project-hour data
$rawData = DB::table('users')
    ->join('time_entries', 'users.id', '=', 'time_entries.user_id')
    ->join('projects', 'time_entries.project_id', '=', 'projects.id')
    ->whereBetween('time_entries.spent_on', [$debut, $fin])
    ->selectRaw(
        'users.id as user_id, users.name as user_name, 
         projects.id as project_id, projects.name as project_name, 
         SUM(time_entries.hours) as total_hours'
    )
    ->groupBy('users.id', 'projects.id')
    ->get();

// Restructure into nested user → projects
$users = $rawData->groupBy('user_id')->map(function ($userProjects) {
    // Grab user details from the first entry
    $user = [
        'id' => $userProjects->first()->user_id,
        'name' => $userProjects->first()->user_name
        // Add other user fields you need here
    ];
    
    // Map projects for the user
    $user['projects'] = $userProjects->map(function ($item) {
        return [
            'id' => $item->project_id,
            'name' => $item->project_name,
            'total_hours' => $item->total_hours
        ];
    });
    
    return $user;
});

Quick Notes #

• If you're using MySQL in strict mode, ensure all non-aggregated fields in your selectRaw are included in the groupBy clause (like we did with users.id and projects.id).
• Adjust the selected fields (like user_name, project_name) to match your actual table column names.

内容的提问来源于stack exchange，提问作者jenoh