I am trying to work out how to get average answers for the following groups, but I cannot work this out!!
Here is my table set ups.
question_groups hasMany questions
questions ManyToMany seats
questions HasMany question_items
seats Has Many submissions
seats ManyToMany versions
submissions hasMany answers
I would like to return the following structure
{ name: "Comfort", labels: [ "Seat A", "Seat B5", "Seat C", "Seat D" ] data: [2.5, 2.9, 1.2, 4.5] }
Basically, how would I find the average value for an answer, grouped by Seat and question groups.
I have gotten the folloing MySQL code, but it doesn't look right and then I would need to format it to get it to look above.
SELECT question_groups.name as 'group', CONCAT(seats.name, ' - ',
versions.name) AS 'seat', avg(answers.value) AS 'value'
FROM question_groups
JOIN questions ON questions.question_group_id = questions.id
JOIN questions_seats ON questions_seats.question_id = questions.id
LEFT JOIN seats ON seats.id = questions_seats.seat_id
LEFT JOIN seats_versions ON seats.id = seats_versions.seat_id
LEFT JOIN versions ON versions.id = seats_versions.version_id
JOIN submissions ON submissions.seat_id = seats.id
JOIN answers ON submissions.id = answers.submission_id
GROUP BY question_groups.name, seats.name, versions.name
I would imagine this is easier with eloquent using API resources, but I don't know how to work the many to many parts and get the averages for the groups