I am new to advance eloquent, I am just trying to sum multiple rows through query itself. Here is how it looks like:
我是一个新到的雄辩者，我只是试着通过查询本身来总结多行。它是这样的:
return $this->quotes->with('quotedetails')->orderBy('created_at', 'DESC')->paginate(10);
Here is the result:
这里是结果:
{
"current_page":1,
"data":[
{
"id":2,
"subject":"demo link data",
"valid_start":"2017-11-14 00:00:00",
"valid_end":"2017-11-21 00:00:00",
"bankaccount_id":52,
"status_id":17,
"user_id":1,
"recurring_subject":null,
"recurring_start":"2017-11-14 00:00:00",
"recurring_period":null,
"recurring_amount":null,
"cash":null,
"created_at":"2017-11-14 11:52:37",
"updated_at":"2017-11-14 11:58:33",
"deleted_at":null,
"customer_id":1,
"quotedetails":[
{
"id":2,
"quotes_id":2,
"quantity":3,
"description":"testing data",
"price":400,
"discount":10,
"total":1080,
"tax":3,
"created_at":"2017-11-14 11:50:08",
"updated_at":"2017-11-14 11:58:33"
},
{
"id":3,
"quotes_id":2,
"quantity":1,
"description":"testing",
"price":200,
"discount":10,
"total":180,
"tax":3,
"created_at":"2017-11-14 11:52:37",
"updated_at":"2017-11-14 11:52:37"
}
]
},
{
"id":1,
"subject":"demo",
"valid_start":"2017-11-14 00:00:00",
"valid_end":"2017-11-15 00:00:00",
"bankaccount_id":42,
"status_id":14,
"user_id":1,
"recurring_subject":null,
"recurring_start":"2017-11-14 00:00:00",
"recurring_period":null,
"recurring_amount":null,
"cash":null,
"created_at":"2017-11-14 11:50:08",
"updated_at":"2017-11-14 16:56:54",
"deleted_at":null,
"customer_id":1,
"quotedetails":[
{
"id":1,
"quotes_id":1,
"quantity":2,
"description":"data",
"price":200,
"discount":10,
"total":360,
"tax":5,
"created_at":"2017-11-14 11:50:08",
"updated_at":"2017-11-14 12:06:18"
}
]
}
],
"first_page_url":"http://localhost/foduu-ecommerce-cms/public/backend/quotes?page=1",
"from":1,
"last_page":1,
"last_page_url":"http://localhost/foduu-ecommerce-cms/public/backend/quotes?page=1",
"next_page_url":null,
"path":"http://localhost/foduu-ecommerce-cms/public/backend/quotes",
"per_page":10,
"prev_page_url":null,
"to":2,
"total":2
}
From the above json I want to get the sum of all quotedetails.total but how can I get it done. I am not sure how this should be done. Here is how I am querying in my model (Quotes)
从上面的json中，我希望得到所有quotedetails的总和。总的来说，但是我怎么做呢?我不知道该怎么做。下面是我在模型中查询的方式(引用)
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",
"valid_start":"2017-11-14 00:00:00",
"valid_end":"2017-11-21 00:00:00",
"bankaccount_id":52,
"status_id":17,
"user_id":1,
"recurring_subject":null,
"recurring_start":"2017-11-14 00:00:00",
"recurring_period":null,
"recurring_amount":null,
"cash":null,
"created_at":"2017-11-14 11:52:37",
"updated_at":"2017-11-14 11:58:33",
"deleted_at":null,
"customer_id":1,
"quotesum": 1260
},
{
"id":1,
"subject":"demo",
"valid_start":"2017-11-14 00:00:00",
"valid_end":"2017-11-15 00:00:00",
"bankaccount_id":42,
"status_id":14,
"user_id":1,
"recurring_subject":null,
"recurring_start":"2017-11-14 00:00:00",
"recurring_period":null,
"recurring_amount":null,
"cash":null,
"created_at":"2017-11-14 11:50:08",
"updated_at":"2017-11-14 16:56:54",
"deleted_at":null,
"customer_id":1,
"quotesum": 360
]
}
],
"first_page_url":"http://localhost/foduu-ecommerce-cms/public/backend/quotes?page=1",
"from":1,
"last_page":1,
"last_page_url":"http://localhost/foduu-ecommerce-cms/public/backend/quotes?page=1",
"next_page_url":null,
"path":"http://localhost/foduu-ecommerce-cms/public/backend/quotes",
"per_page":10,
"prev_page_url":null,
"to":2,
"total":2
}
Suggest any output for this. Or query to solve the issue.
建议对此的任何输出。或查询解决问题。
Thank you (in advance)!
谢谢你(提前)!
2 个解决方案
#1
3
$query->withCount([
'quotedetail AS quotesum' => function ($query) {
$query->select(DB::raw("SUM(total) as quotesum"));
}
]);
#2
1
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.
反方:结果中的更多数据意味着收集工作的增加，尤其是在大数据的情况下。
#1
3
$query->withCount([
'quotedetail AS quotesum' => function ($query) {
$query->select(DB::raw("SUM(total) as quotesum"));
}
]);
#2
1
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.
反方:结果中的更多数据意味着收集工作的增加，尤其是在大数据的情况下。