I have two tables:
Items
| id | name | downloads | category_id |
|----|--------|-----------|-------------|
| 1 | Item 1 | 5 | 1 |
| 2 | Item 2 | 7 | 1 |
| 3 | Item 3 | 23 | 2 |
| id | name |
|----|------------|
| 1 | Somecategory |
| 2 | Someothercategory |
| 3 | Somecategory2 |
$cats = Category::where('name', 'like', '%'.$query.'%')
->orderBy('parent', 'desc')
->orderBy('name', 'asc')
->take(10)
->get();
Someothercategory
It is a matter of joining the tables, grouping by the category name and using sum on the downloads.
Using SQL, that could be solved using:
SELECT c.name, SUM(downloads) AS downloads
FROM items i
INNER JOIN categories c ON i.category_id=c.id
WHERE c.name LIKE '%other%'
GROUP BY c.name
ORDER BY downloads DESC
The next step would be to search how to achieve this using Eloquent, if that's what you desire.
If you want to simplify things, you could also create a View on the database containing this code. With that you would only need to:
SELECT name, downloads
FROM viewCategoryDownloads
WHERE c.name LIKE '%other%'
ORDER BY downloads DESC