Get sum of a specific column from one to one-> many to many relationship

I have a Budgets table where budget is input based on sub head (have column fiscal_year). And have a Works table where work is created based on sub head. Have Bookings table where booking is created based on work (one work may have many booking). Booking table has a column 'actual', means actual booking amount and fiscal_year.

What i want is to get -sub head and fiscal_year wise booking amount sum . I tried creating a new model function in SubHead (getActualWorkBySubHead()), but i can go only one step forward. I can get work count of sub head based. ==>You will understand my above text if you have a look at my model relataion.

My controller returns this:

$data = $this->budget->whereFiscalYear($r->fiscal_year)//->selectRaw('sum(bookings.actual) as sum')
            ->whereHas('subHead',function($q) use($head){
                $q->whereHas('head',function ($q) use($head){
                    $q->where('id',$head);
                });
            })
            ->get();

I do loop in view:

@foreach($data as $key => $t)


        <tr>
            <td></td>
            <th> </th>
            <td> </td>
            <td> </td>
            <td> </td>
            <td>
                

            </td>

            <td></td>
            <td style="font-size: 14px;text-transform: capitalize"></td>
        </tr>

    @endforeach

Booking Model::

public function user()
{
    return $this->belongsTo('App\User','created_by');
}
  public function work()
{
    return $this->belongsTo('App\Work','work_id');
}

Budget Model::

 public function user()
    {
        return $this->belongsTo('App\User','created_by');
    }
    public function subHead()
    {
        return $this->belongsTo('App\SubHead','sub_head_id');
    }

SubHead Model ::

 public function head()
{
    return $this->belongsTo('App\Head','head_id');
}


public function getActualWorkBySubHead()
        {
            return $this->works()->count('sub_head_id');
        }

Work Model::

    public function subHead()
{
    return $this->belongsTo('App\SubHead','sub_head_id');
}

public function bookings()
{
    return $this->hasMany('App\Booking','work_id');
}

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2FmgU4Q
via IFTTT