Question 17300 in Laravel Question posted in
The official Laravel Documentation can be found here.

withSum in deep nested relationship in laravel eloquent

I could not find this in the laravel docs on aggregate relationships

I was able to do something like this

    private function refreshUsers()
    {
        $this->users = User::withSum(['taskTimeSessions'=> function ($query) {
                                    $query->whereMonth('created_at',$this->month)
                                            ->where('is_reconciled',1);
                        }],'session_duration_in_seconds')
                        ->get();
    }

But now I am trying to query what is the total time a Sprint has or at the very least what the individual tasks inside a sprint have so that I can just sum the total of those somehow.

  • Sprint has many SprintTasks (pivot table)
  • SprintTask belongs to one Task
  • Task has many TaskTimeSessions

So I am trying to go find the total time of the TaskTimeSessions

Sprint::with([
'sprintTasks.task'=> function ($query) {
    $query->withSum('taskTimeSessions','session_duration_in_seconds');
   }])
->get();

I am not getting any errors, but not finding the result anywhere when dd

I thought i would get lucky and have something like this work

->withSum('sprintTasks.task.taskTimeSessions', 'session_duration_in_seconds')

But I am getting this error

Call to undefined method AppModelsSprint::sprintTasks.task()

If anyone can help me out with some guidance on how to go about this, even if it doesn’t include withSum it would be much appreciated.

As requested, these are the models.

// Sprint
public function sprintTasks()
    {
        return $this->hasMany(SprintTask::class, 'sprint_id');
    }
    


// SprintTask
protected $fillable = [
        'sprint_id',
        'task_id',
        'is_completed'
    ];

public function task()
    {
        return $this->belongsTo(Task::class,'task_id');
    }

public function sprint()
    {
        return $this->belongsTo(Task::class,'sprint_id');
    }



// Task
public function taskTimeSessions()
    {
        return $this->hasMany(TaskTimeSession::class, 'task_id');
    }



// TaskTimeSessions
protected $fillable = [
        'task_id',
        'session_duration_in_seconds'
    ];
    

public function task()
    {
        return $this->belongsTo(Task::class,'task_id');
    }

Is it possible to abstract this into the model as like

public function totalTaskTime() {
 // using the relationship stuff to figure out the math and return it? 

}

Looking for any advice on what the best approach is to do this.

Right now I am literally doing this in the blade and seems very bad

@php
    $timeTracked = 0;
    
    foreach ($sprint->sprintTasks as $sprintTask) {
        $timeTracked += $sprintTask->task->time_tracked_in_seconds;
    }
@endphp
Tags:

2

Answers


  1. 0

    You have a many to many relation between sprint and task

    For that you can setup a direct relation belongsToMany with sprint_tasks as the pivot table

    // Sprint
public function sprintTasks()
{
    return $this->hasMany(SprintTask::class, 'sprint_id');
}

public function tasks()
{
    return $this->belongsToMany(Task::class, 'sprint_tasks', 'sprint_id', 'task_id')->withPivot('is_completed');
}

    Now you can use that relation to query your needs

    Sprint::with(['tasks'=> function ($query) {
    $query->withSum('taskTimeSessions','session_duration_in_seconds');
}])
->get();
    Login or Signup to reply.
  2. 0

    There is a good package for Laravel for complex relationships – eloquent-has-many-deep. You can use it to build relationships through an unlimited number of tables.

    composer require staudenmeir/eloquent-has-many-deep:"^1.7"

    Sprint.php

    use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentRelationsBelongsToMany;
use StaudenmeirEloquentHasManyDeepHasManyDeep;
use StaudenmeirEloquentHasManyDeepHasRelationships;

class Sprint extends Model
{
    use HasRelationships;

    public function tasks(): BelongsToMany
    {
        return $this->belongsToMany(Task::class, 'sprint_tasks');
    }

    public function taskTimeSessions(): HasManyDeep
    {
        return $this->hasManyDeepFromRelations($this->tasks(), (new Task())->taskTimeSessions());
    }
}

    Task.php

    use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentRelationsHasMany;

class Task extends Model
{
    use HasFactory;

    public function taskTimeSessions(): HasMany
    {
        return $this->hasMany(TaskTimeSession::class);
    }
}

    Result:

    $sprints = Sprint::withSum('taskTimeSessions', 'session_duration_in_seconds')->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search