Level 2
You should assign the relationships on your models first. Then you can do something like this:
$location = Location::find($id)
$total = $location->results->sum('score')
Jul 10, 2023
4
Level 1
Help with Query that has many relationship
Hi, hopefully someone can help. I am trying to get some results. I have a Location table and a Results table. A Location can have many Results which contains a filed called score.
Location
- id
- name
Result
- id
- location_id
- score
I am trying to get the Locations with grouped results as a total so that we can see what the best score is between the locations.
I can do a SQL query on my Result table as follows: SELECT location_id, SUM(score) as total FROM results GROUP BY location_id
However I want to have the Location name so that my results look like:
- location_id
- location_name
- total
Any idea what is my best options here? I could get all of the locations and then run queries on them to get the total score but I thought there should be a better / more performant way?
Thanks in advance
Level 1
@voiceinthedark Yes i've set that up so a location can get the results I just wasnt sure about doing:
$locations = Location::all(); foreach($locations as $location) { $location['total'] = $location->results->sum('score'); }
Level 11
@c-andrews do something like Location::withSum('results', 'score')->get();
1 like
Level 1
@PovilasKorop Thank you, that works a treat and is just what i was looking for.
Please or to participate in this conversation.