Alex Alex - 6 months ago 18
MySQL Question

Order categories by number of downloads of the items in them

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 |


Categories

| id | name |
|----|------------|
| 1 | Somecategory |
| 2 | Someothercategory |
| 3 | Somecategory2 |


I am currently using a basic query:

$cats = Category::where('name', 'like', '%'.$query.'%')
->orderBy('parent', 'desc')
->orderBy('name', 'asc')
->take(10)
->get();


But I would like the categories to be sorted by the number of their items' downloads.

So if someone searches for "some" to return
Someothercategory
first, because its item (Item 3) has the most downloads.

Thanks!

Edu Edu
Answer

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
Comments