laravel eloquent with sum multiple rows from linked table

Question

I have a table Quotes that has a one-to-many relationship with the QuoteDetails table.

The table structure looks like this:

return $this->quotes->with('quotedetails')
    ->orderBy('created_at', 'DESC')->paginate(10);

Results in:

{
   "current_page":1,
   "data":[
      {
         "id":2,
         "subject":"demo",
         "quotedetails":[
            {
               "id":2,
               "quotes_id":2,
               "description":"testing data",
               "total":1080
            },
            {
               "id":3,
               "quotes_id":2,
               "description":"testing",
               "total":180
            }
         ]
      },
      {
         "id":1,
         "subject":"demo",
         "quotedetails":[
            {
               "id":1,
               "quotes_id":1,
               "description":"data",
               "total":360
            }
         ]
      }
   ],
   "per_page":10,
   "prev_page_url":null,
   "to":2,
   "total":2
}

I want to include the sum of quotedetails.total rather than the full list of QuoteDetails rows. I am not sure how this should be done.

Here is how I am querying in my model (Quotes)

public function quotedetails(){
        return $this->hasMany('App\Models\QuotesDetail', 'quotes_id', 'id');
    }

How can i get the total sum.

Here is how i expect the output

{
   "current_page":1,
   "data":[
      {
         "id":2,
         "subject":"demo link data",
         "quotesum": 1260
      },
      {
         "id":1,
         "subject":"demo",
         "quotesum": 360
      }
   ],
   "per_page":10,
   "prev_page_url":null,
   "to":2,
   "total":2
}

user_8933794 · Answer

$query->withCount([
'quotedetail AS quotesum' => function ($query) {
            $query->select(DB::raw("SUM(total) as quotesum"));
        }
    ]);

user_5704410 · Answer

I understand that you want to do it by query, But taking this without even query you'll be able to transform the paginated data this way, giving this example:

$quotes = Quotes::whereNotNull('id')->with('quotedetails')
    ->orderBy('created_at', 'DESC')->paginate();

//transform the data in the Paginate instance
$quotes->getCollection()->transform(function($quote){
     $quote->quotesum = $quote->quotedetails->sum('total');
     unset($quote['quotedetails']);
     return $quote;
});

The data in the paginated result is transformed and you have what you requested.

Pro: More possibilities to do more with your response without adding any query.

Con: More data in the result mean more work on the collection especially in case of large data.

laravel eloquent with sum multiple rows from linked table

2 Answers