0

There are two table videos and reactions.

enter image description here

I want to sort videos by likes descending. reactions table is same for likes and dislikes. (I did not want to create two different tables for likes and dislikes). There is reaction column in the reactions table where reaction = 1 means likes, reaction = 2 means disslikes

The thing is that my code without where('reactions.reaction', 1) returns all videos sorted by total reactions (likes + disslikes) and I need to sort by only likes.

If add where('reactions.reaction', 1) then my query will returns only videos with likes instead of all the videos.

I want to get all videos from the table sorted by likes and not just liked videos. What should I do?

$videos = Video::select('videos.id', DB::raw('count(reactions.id) as total'))
->leftJoin('reactions', 'reactions.at_video', '=', 'videos.id')
// ->where('reactions.reaction', 1) // I need this for only reactions
->groupBy('videos.id')
->orderBy('total', 'DESC')
->get();

dd($videos);
WEB TM
  • 33
  • 6
  • I think you should use join instead of left join, so if a video does not have any reactions the query will not select them. – fake97 Jul 27 '21 at 13:20
  • LEFT JOIN is right way becouse of as we can read LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). https://www.w3schools.com/sql/sql_join_left.asp – WEB TM Jul 27 '21 at 14:09
  • okay than does this help you ->where(function ($q){ $q->where('reactions.reaction', 1)->orWhereNull(''reactions.reaction');}) – fake97 Jul 27 '21 at 14:13
  • Thanks man. You saved me. – WEB TM Jul 27 '21 at 15:41

1 Answers1

0

Massive thanks to fake97 for the solution.

I needed to change ->where('reactions.reaction', 1) To >where(function ($q){ $q->where('reactions.reaction', 1)->orWhereNull(''reactions.reaction');})

See the code below

$videos = Video::select('videos.id', DB::raw('count(reactions.id) as total'))
->leftJoin('reactions', 'reactions.at_video', '=', 'videos.id')
->where(function ($q){ 
  $q->where('reactions.reaction', 1)->orWhereNull('reactions.reaction');
})
->groupBy('videos.id')
->orderBy('total', 'DESC')
->get();
      
dd($videos);
WEB TM
  • 33
  • 6