How to sort by field?

Question

css

Good evening!
There are two tables: posts (id, name, text) and comments (id, text, post_id)
How to sort posts by number of comments?
Found and tried via join:

$posts = Post::with('comments')
            ->join('comments', 'comments.post_id', '=', 'posts.id')
            ->select('posts.*', 'comments.*')
            ->groupBy('posts.id')
            ->orderBy(DB::raw('COUNT(`posts`.`id`)'), 'asc')
            ->get();

But it says that the request is not correct Syntax error or access violation

Answer 1

Briefly, like this

Answer 2

We assume that there is a Post model with a comments() relation that hasMany

$postsWithCountOfComments = Post::withCount("comments")->get();
$sortedPosts = $postsWithCountOfComments->sortByDesc('comments_count');

In laravel, for a reason, collections instead of arrays are returned in all database functions. Use them, they are great.
https://laravel.com/docs/5.4/eloquent-relationship...
https://laravel.com/docs/5.4/collections

Answer 3

1. If groupBy('posts.id'), then you cannot select('comments.*'). For it is ambiguous
Generally speaking, at groupBy ('posts.id') it is possible only select ('posts.id') + group functions. Although MySQL will skip some shitty code, that's no reason to write it that way.
2. The number of comments is COUNT(comments.post_id) or simply COUNT(*). And COUNT(posts.id) with GROUP BY posts.id - obviously, it will always be 1
3. If it's still an error, then specify the resulting SQL
4. Well, in general, the correct way is to create a new posts.count_comments field with an index and recalculate it when comment change.

Answer 4

SELECT COUNT ( id ) AS cntd 
FROM comments 
GROUP BY post_id 
ORDER BY cntd

So it's a standard request for a count.