Fastest way to order table by related table column

user_8649887 0
Viewed 156

I have 2 tables comments and images

I'm trying to order the comments that have an image first and then comments without images.

I used this query below to solve this issue but it's very slow on large data it takes about 12 seconds 

$data = Comment::withCount([
        'images' => function ($query) use ($shop)
        {
            $query->where('shop_name', $shop);

        },
    ])->where('shop_name', $shop)->orderBy('images_count', 'desc')->paginate(10);

How can I improve the performance or is there any other way to get similar results in faster way ?

laravel-5.5 laravel-query-builder laravel mysql sql
2 Answers

The problem lies in how Laravel makes withCount() work - it will generate something like this:

SELECT `comments`.*, 
   (SELECT Count(*) 
    FROM   `images` 
    WHERE  `comment_id`.`id` = `comments`.`id` 
           AND `images`.`shop_name` = 'your shop') AS `images_count` 
FROM   `comments` 
WHERE  `shop_name` = 'your_shop' 
ORDER  BY `images_count`

This will force MySQL to execute count() subquery for every comment of specified shop.

What you need to do here is to make this correlated subquery (that executes for every row) into an independent query (that executes only once) and then utilize joins to let MySQL pair it all up:

$imagesCountQuery = DB::table('comments')
    ->selectRaw('comments.id AS comment_id, COUNT(comments.id) AS images_count')
    ->join('images', 'images.comment_id', '=', 'comments.id') /* !!! */
    ->where('images.shop_name', '=', $shop)
    ->groupBy('comments.id');

$data = Comment::joinSub($imagesCountQuery, 'images_count_sub', function ($join) {
    $join->on('comments.id', '=', 'images_count_sub'.'comment_id'); /* !!! */
})->where('shop_name', $shop)->orderBy('images_count_sub.images_count', 'desc')->paginate(10);

!!! - this lines should be modified to represent your comment's images relation. In this example I just assumed it was hasMany relation since you didn't point that out in your question.

user_93487480

you need to create a hasMany relation in Comment model. 

class Comment extends \Eloquent {  

        public function images()
        {
            return $this->hasMany('Images', 'comment_id');
        } 
     }

Now you can use below query to fetch records. 

$comments = Comments::with('images')->get()->sortBy(function($comment)
    {
        return $comment->images->count();
    });
user_37877860
Related