Laravel filter nested relationship

Question

So I have three models: Volunteer, Task and Payment. A Volunteer can have many (has many relationship) tasks and a task can have many (another has many relationship) payments.

class Volunteer

public function tasks()
{
    return $this->hasMany(Task::class);
}

class Task

  public function volunteer()
{
    return $this->belongsTo(Volunteer::class);
}

 public function payments()
{
    return $this->hasMany(Payment::class);
}

class Payment 

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

Now I want to query all volunteers with unpaid/ partially paid tasks. So, basically I want to filter a volunteer's tasks where each task's amount should equal the sum of all payments linked to that particular task.

I tried using whereHas and with but I don't seem to be able to filter the tasks properly.

I've managed to do it with joins but was wondering if it's possible to with whereHas or with. Below is the code:

Volunteer::select('volunteers.id', 'volunteers.name', 'tasks.amount', DB::raw('SUM(payments.amount) as amount_paid'))
        ->join('tasks', 'tasks.volunteer_id', '=', 'volunteers.id')
        ->leftJoin('payments', 'payments.task_id', '=', 'tasks.id')
        ->groupBy('volunteers.id', 'volunteers.name', 'tasks.amount')
        ->havingRaw('amount_paid >= tasks.amount')
        ->get();

Any help would be appreciated!

Answer 1

I would like to suggest something else which is adding a column in tasks table that indicates if the task is [paid, unpaid or partially paid] in your tasks migration like so

$table->unsignedTinyInteger('paid_status')->default(0); // 0 -> unpaid, 1 -> partially paid, 2 -> paid

then each time the volunteer makes a payments you will do a simple check to update tasks.paid_status something like checking the total paid_amount and task amount

then using Laravel hasManyThrough in Volunteer model like so

public function payments()
{
   return $this->hasManyThrough(
       'App\Payment',
       'App\Task'
  );
}

now to get your data you will do so

// unpaid tasks
Volunteer::query()->first()->payments()->where('tasks.paid_status', '0')->get();
// partially paid tasks
Volunteer::query()->first()->payments()->where('tasks.paid_status', '1')->get();
// paid tasks
Volunteer::query()->first()->payments()->where('tasks.paid_status', '2')->get();

you can read more about HasManyThrough Here