I am trying to get a sum layered down 2 relations.
I have 3 related models: Question hasMany Answer hasMany Score
On the Score model there is column called value
I want to be able to order the Questions by the sum of the value columns.
I have tried multiple ways like using
Question::with(['answers' => function($q) {
$q->withSum('scores', 'value');
}])->orderByRaw('sum(answers.scores_sum_value)')->get();
but get
Column not found: 1054 Unknown column 'answers.scores_sum_value' in 'order clause'
I also tried to see if there was a way to do a layered withSum to bring the total sum of all scored on the Question model but that was no luck.
One more thing, there is some Questions with no Answers yet and those should have a score of 0 when ordering.
Any ideas? Thanks!
@ajsheldon93 How about this?
$questions = Question::leftJoin('answers', 'questions.id', '=', 'answers.question_id')
->leftJoin('scores', 'answers.id', '=', 'scores.answer_id')
->select('questions.*', DB::raw('SUM(scores.value) as total_score'))
->groupBy('questions.id')
->orderByRaw('total_score DESC')
->get();
@tisuchi This worked! I knew there had to be something with joins, but I wasn't sure how to impliment it. Thank you!
Please or to participate in this conversation.