John Davidson

Message:

I would like to order my products by the those who do have the most likes. I have one products table, one likes table and one users table.

This is how my likes table looks like:
id, user_id, product_id, value

The value can either be 1 or -1 which is equal to one like or one dislike.

In my products model I have defined those relationships:

public function likes()

{

    return $this->belongsToMany('App\Models\User', 'products_likes', 'product_id', 'user_id')->withPivot('value')->withTimestamps();

}



public function productLikes()

{

    return $this->hasMany('App\Models\ProductLike');

}

However, now I would like to get the first 20 products which have the most sum of likes.
This is how I tried to solve this, however, I cannot solve my problem...

$products = Product::select('products.*', DB::raw('SUM(products_likes.value) as sumLikes'))

            ->join('products_likes', 'products_likes.product_id', '=', 'products.id')

            ->orderBy('sumLikes', 'DESC')

            ->get();

However, this results in a weird group by sql problem:

QLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select `products`.*, SUM(products_likes.value) as sumLikes from `products` inner join `products_likes` on `products_likes`.`product_id` = `products`.`id` order by `sumLikes` desc) 

Does anyone knows how I can archive this?
I want to have my database to perform the ordering job and not Laravel. I have found some solutions here as well but they suggest to perform an all() and with("likes") query and use sortByDesc after the query got executed (here). But this brings down performance.

Kind regards

---

My Answer:
>hasMany('App\Like');_}_public function likesCount()_{_ return $this->hasMany('App\Like')->selectRaw('product_id, sum(value) as total_likes')->groupBy('product_id');_}_And in my controller I would like to order my products by the sum of likes. Here is how I can achieve this:_$products = Product::with('likesCount')->orderBy('likesCount.total_likes', 'desc')->get();_This will order my products by the sum of likes in descending order.