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
Every time I download an "Upload" the value in the downloads column is increased by 1.
What I want to do, but couldn't figure out how to even start is to select top 10 categories based on the downloads
column in the uploads
table. So, if the total of the downloads
column is the biggest for all uploads from a given category this category should be number one and so on to number ten.
Can this be achieved with eloquent or just SQL and how? Any tips are appreciated!
Here is how I've set up my relationships in the models :
Category :
public function uploads(){
return $this->belongsToMany('App\Upload');
}
Upload :
public function category(){
return $this->belongsToMany('App\Category');
}
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With