How to sort by number of records from related Laravel table?

Question

SQL

There are two tables:

1. adverts:
- id
2. likes:
- id
- advert_id
- user_id
- dislike

Each like/dislike has its own record in the likes table.
If the user put a dislike, then the dislike column = 1, if like, then = 0.

How can you sort ads by the number of likes (!) , together with pagination of 10 entries .
That is, the first in the list to display those entries that have more likes.
And the most recent, those with more dislikes.

Answer 1

SELECT `adverts`.*,
((SELECT COUNT(`likes`.`id`) FROM `likes` WHERE `likes`.`advert_id` = `adverts`.`id` AND `dislike` = 0) - (SELECT COUNT(`likes`.`id`) FROM `likes` WHERE `likes`.`advert_id` = `adverts`.`id` AND `dislike` = 1)) AS `ALL_LIKES`
FROM `adverts`
ORDER BY `ALL_LIKES` DESC

Will I transfer to ORM Laravel myself?
UPD:

$rs = Advert:selectRaw('`adverts`.*, ((SELECT COUNT(`likes`.`id`) FROM `likes` WHERE `likes`.`advert_id` = `adverts`.`id` AND `dislike` = 0) - (SELECT COUNT(`likes`.`id`) FROM `likes` WHERE `likes`.`advert_id` = `adverts`.`id` AND `dislike` = 1)) AS `ALL_LIKES`')->orderBy('ALL_LIKES', 'DESC')->get();

PS:
Think about storing the number of likes/dislikes or their ratio directly in the ad field...i.e. update this field with every like/dislike... and then you will have to pull the second table less to display ads...

Answer 2

The simplest and most efficient way is to add the scores field to the adverts table
Increment +10 points for a like event Decrement
-10 points for a dislike event Sort by
orderByDesc scores to favorites and many other metrics that are simply covered by tests (checking that scores increased / decreased during the event) and thus form a cool sorting)