Summing multiple fields in Laravel

Question

I am wondering if it is possible to take the sum of several fields in one query using the free query designer.

I currently have two tables: events and participants. Participants are events and have two fields: total_raised and total_hours. I want to select all events and the total number raised / total hours spent on this event. Now, if I just used SQL, I would do something like:

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 cannot find a way to take several sums at once using the free query designer. Is there a way to do what I'm trying to do using my free time, or just make it an unprocessed SQL query?

Answer 1

You can use sum() ie:

 $q = DB::table('events') ->join('attendees', 'events.id', '=', 'attendees.event_id') ->sum('total_raised') ->sum('total_hours'); 

If this does not work, you can try:

 ... ->get( array( 'events.id', DB::raw('SUM(attendees.total_raised)'), DB::raw('SUM(attendees.total_hours)') ) ); 

Answer 2

Building an answer to simones. You can do this by doing basically 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 situation. If it were on the administrator / CMS side, I would be inclined to this decision. If it is at the front end, this should be done in a single request, which will be faster. Depending on the content, this may or may not be a significant difference.

 $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' ), )); 

Answer 3

I do the same in my project. Here is the solution I found. I am using Laravel 5.2 "Eloquent", here is the expression "Brightness".

This is the expression that I use in my project. Please make changes to suit your needs.

 $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(); 

The same that you can use for your request, for example

 $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();