是否可以按多个表的总数进行排序? [英] Is it possible to order by the total count of multiple tables?

问题描述

我无法使该查询正常工作！

I can't get this query to work!

return Post::selectRaw('likes_count + comments_count as total_count')
            ->withCount(['likes', 'comments'])
            ->groupBy('posts.id')
            ->orderByDesc('total_count')
            ->paginate();

下面这种方式起作用，但是我想要所有的计数和性能！

It works this way below but I want all the counts and something performant!

return Post::leftJoin('likes', function ($join) {
            $join->on('posts.id', 'likes.likable_id')
                ->where('likes.likable_type', (new Post)->getMorphClass());
        })->leftJoin('comments', function ($join) {
            $join->on('posts.id', 'comments.commentable_id')
                ->where('comments.commentable_type', (new Post)->getMorphClass());
        })
            ->selectRaw('posts.*, count(likes.id) + count(comments.id) as total_count')
            ->groupBy('posts.id')
            ->orderByDesc('total_count')
            ->paginate();

推荐答案

您可能只需要对第一个查询进行一些更改:

You probably just need to change a little bit the first query:

return Post::selectRaw('Count(likes.id) + Count(comments.id) as total_count')
        ->withCount(['likes', 'comments'])
        ->groupBy('posts.id')
        ->orderByRaw('(Count(likes.id) + Count(comments.id)) desc')
        ->paginate();

这篇关于是否可以按多个表的总数进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！