Order categories by number of downloads of the items in them

Question

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!

Answer 1

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

Order categories by number of downloads of the items in them

Sign up