Choosing the top ten categories based on a column in another table with eloquent

Question

I have three tables: uploads , categories and a many-to-many table called category_upload .

The tables look like this:

 **categories** id name **uploads** id name downloads(integer) **category_upload** id category_id upload_id 

Each time I download “Download,” the value in the “Downloads” column is incremented by 1.

What I want to do, but could not figure out how to get started, is to select the top 10 categories based on the downloads column in the uploads table. So, if the total amount of the downloads column is the largest for all downloads from this category, this category should be number one, etc. To number ten.

Can this be achieved with eloquent or just SQL and how? Any advice is appreciated!

Here is how I established my relationship in models:

Category:

 public function uploads(){ return $this->belongsToMany('App\Upload'); } 

Loading:

 public function category(){ return $this->belongsToMany('App\Category'); } 

Answer 1

 Select category_id, uploads From ( Select category_id, SUM(downloads) as uploads From uploads as u Left join upload_category as uc on uc.id=u.upload_id Left join category as c on c.id=uc.category_id Group by category_id )as t Order by uploads desc Limit 10 

Answer 2

Your Category Model

 public function downloads() { return $this->hasMany('App\Upload','category_upload','category_id','upload_id'); } 

In your controller ( Catagory )

try it

 Category::select("*")->with(['downloads' => function($q){ $q->oderBy('downloades','desc')->take(10)->get(); })