Sort model based on a different table

I have a question/answer application with 3 tables, Questions, Answers, Answers_likedislike. I want to sort the Answers by the total (up-down) likes. I can calculate the rank of answers by 'SUM(up) -SUM(down) as rank' however do not know how to accommodate it in the model:

Here is the model and what I am intend to do:

public function answers() { return $this->hasMany("App\Answer")->orderByRaw( SOMETHING LIKE SELECT SUM(up) -SUM(down) as rank from answers_likedislike where answer_id == WHERE TO GET THE ID?) }

Can someone please help?

sr57

5 years ago

Level 39

1 - to get the id , you have to join with the table answer

2- to use SUM you have to GROUP

You can adapt from this syntax

SELECT id,UM(up) -SUM(down) as rank FROM answer JOIN answers_likedislike ON id=answer_id GROUP BY id

1 like

Tray2

5 years ago

Level 73

You could use something like this

$answers = Answers::withSum('likes')->orderBy('likes_sum')->get();

1 like

Please or to participate in this conversation.