在 Laravel 中对多个字段求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15369005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Summing over multiple fields in Laravel
提问by Jonathan
I'm wondering if it is possible to take the sum of multiple fields in one query using the fluent query builder.
我想知道是否可以使用流畅的查询构建器在一个查询中获取多个字段的总和。
I currently have two tables: events and attendees. Attendees belong to events and have two fields: total_raised and total_hours. What I want to do is select all events and the total amount raised/total number of hours spent on that event. Now, if I were just using SQL I would do something to the effect of:
我目前有两个表：事件和与会者。参加者属于事件并有两个字段：total_raised 和 total_hours。我想要做的是选择所有事件以及在该事件上筹集的总金额/总小时数。现在，如果我只是使用 SQL，我会做一些事情：
SELECT Event.id, sum(Attendees.total_raised), sum(Attendees.total_hours)
FROM Events JOIN Attendees ON Events.id = Attendees.event_id
GROUP BY Event.id
However, I can't seem to find a way to take multiple sums at once using the fluent query builder. Is there any way to do what I'm trying to do using fluent, or should I just make it a raw SQL query?
但是，我似乎无法找到一种使用 fluent 查询构建器一次计算多个总和的方法。有什么方法可以使用 fluent 做我想做的事情，还是应该让它成为原始 SQL 查询？
采纳答案by Simone
You can use sum()i.e.:
您可以使用sum()即：
$q = DB::table('events')
->join('attendees', 'events.id', '=', 'attendees.event_id')
->sum('total_raised')
->sum('total_hours');
If that doesn't work you can try:
如果这不起作用，您可以尝试：
...
->get(
array(
'events.id',
DB::raw('SUM(attendees.total_raised)'),
DB::raw('SUM(attendees.total_hours)')
)
);
回答by Collin James
Building on simones answer. You could do this by essentially running two queries.
以西蒙斯的回答为基础。您可以通过本质上运行两个查询来做到这一点。
$query = DB::table('events')->join('attendees', 'events.id', '=', 'attendees.event_id');
$raised = $query->sum( 'total_raised' );
$hours = $query->sum( 'total_hours' );
It depends on the situation. If it were on the admin/CMS side of things I'd be lean towards this solution. If it is on the front end it should be done in a single query which will be faster. Depending on the content it may or may not be a significant difference.
这取决于实际情况。如果是在管理/CMS 方面，我会倾向于这个解决方案。如果它在前端，它应该在单个查询中完成，这样会更快。根据内容的不同，它可能有也可能没有显着差异。
$result = DB::table('events')->join('attendees', 'events.id', '=', 'attendees.event_id')
->get( array(
DB::raw( 'SUM(attendees.total_raised) AS raised' ),
DB::raw( 'SUM(attendees.total_hours) AS hours' ),
));
回答by Nikunj K.
I am doing the same thing in my project, Here is the solution which I found. I am using Laravel 5.2 Eloquent here is the Eloquent statement.
我在我的项目中做同样的事情，这是我找到的解决方案。我使用的是 Laravel 5.2 Eloquent，这里是 Eloquent 语句。
This statement which I use in my project, Please made change according to your need.
我在我的项目中使用的这个语句，请根据您的需要进行更改。
$result = self::select("*", DB::raw('SUM(auction_amount) as total_auction_amount') , DB::raw('SUM(commission_amount) as total_commission_amount'),
DB::raw('SUM(deposit_amount) as total_deposit_amount'))
->groupBy('cp_user_id')
->get()
->toArray();
Same way you can use for your query like
您可以使用相同的方式进行查询，例如
$result = self::select("*", DB::raw('SUM(auction_amount) as total_auction_amount') , DB::raw('SUM(Attendees.total_raised) as total_raised'),
DB::raw('SUM(Attendees.total_hours) as total_hours'))
->with('Attendees')
->groupBy('id')
->get()
->toArray();
