laravel many to many with chaining where clause

Question

I have a given table :

tools     toolparts      parts     part_details
-----     ---------      -----     ------------
id*       id*            id*       id*
name      tool_id        name      part_id
          part_id                  total (int) 
-----     ---------      -----     ------------

the relation between Tools and Parts is ManyToMany. and the relation between parts and part_details is one to many.

with Laravel model, how can I get tool with part that has the biggest part_details.total ??

//tool model
public function parts()
{
    return $this->belongsToMany('App\Part', 'tool_part');
}

//part model
public function tools()
{
   return $this->belongsToMany('App\Tool', 'tool_part')
}

public function details(){
    return $this->hasMany('App\Part_detail');
}

//partDetail model
public function part(){
    return $this->belongsTo('App\Part');
}

Controller

public function index()
{
  $tools = Tool::with('parts', 'parts.details')->has('parts')->get();
  return $tools;
}

what I expected is something like :

Controller

public function index()
{
  $tool = Tool::with('SinglePartThatHasHigestTotalInPartDetail');

}

Any Idea ??

Answer 1

I Manage my problem with "hacky" ways. if someone have a better and more elegant solution, please tell me.

//tool model

public function partWithHighestTotalDelivery($trans_date = null){
    if (is_null($trans_date)) {
        $trans_date = date('Y-m-d');
    }

    $parts = $this->parts;

    $highest_total_delivery = 0;

    foreach ($parts as $key => $part) {
        $part->detail;

        $total_delivery = $part->first_value;

        if (isset( $part->detail->total_delivery )) {
            $total_delivery += $part->detail->total_delivery;     
        }

        if ($highest_total_delivery < $total_delivery ) {
           $highest_total_delivery = $total_delivery;

           $part->total_delivery = $highest_total_delivery;

           $result = $part;
        }   

    }

    if (!isset($result)) {
        $result = null;
    }

    $this->part = $result;
}

In controller i have :

public function index(Request $request){
    $tools = Tool::has('parts')
    ->get();

    $tools->each(function($tool){
      $tool->partWithHighestTotalDelivery();
    });

    return $tools;
}

with this, I need to run tool->partWithHighestTotalDelivery() tools.count times. which is take noticeable process if the tools is many.

and also, the code I post and the question I ask has a slightly difference.that's for a simplicity sake's