Getting the top 10 stores within a category

I have the following query. I know there's an easier way to do this without the sub queries, or at least optimizing how they're used. I've read about cross joins and using HAVING but cannot figure out how to make this better as I know it's pretty bad.

Basically what I'm trying to do is get the top 10 stores within a category. For this category it is category with an id of 9 as you can see in the where clause. I am categorizing each individual user based on the most occurring category of the products they have. And then reordering the results by most orders and getting the first 10 results.

EDIT:

c.id = 9, which should be c.id = '$categoryId' (edited) is representative of where a numeric value would be passed into the query to get the top stores specific to a category id.

SELECT u.id, 
       u.avatar, 
       u.storeName,
       (SELECT COUNT(o.id) FROM orders o WHERE o.storeId = u.id) AS orders
FROM users u
WHERE (SELECT c.id
       FROM categories c, products p
       WHERE c.id = p.categoryId
       AND p.userId = u.id
       GROUP BY p.categoryId
       ORDER BY COUNT(p.id) DESC
       LIMIT 1) = '$categoryId'
ORDER BY orders DESC
LIMIT 10

There are some things that you don't need here

SELECT u.id, 
       u.avatar, 
       u.storeName,
       (SELECT COUNT(o.id) FROM orders o WHERE o.storeId = u.id) AS orders
FROM users u
WHERE (SELECT c.id
       FROM categories c, products p
       WHERE c.id = p.categoryId
       AND p.userId = u.id
       GROUP BY p.categoryId
       ORDER BY COUNT(p.id) DESC
       LIMIT 1) = 9
ORDER BY orders DESC
LIMIT 10

like the ORDER BY and the LIMIT 1 and the GROUP BY in the Select statement that is nested in your where clause.

SELECT u.id, 
       u.avatar, 
       u.storeName,
       (SELECT COUNT(o.id) FROM orders o WHERE o.storeId = u.id) AS orders
FROM users u
WHERE (SELECT c.id
       FROM categories c, products p
       WHERE c.id = p.categoryId
       AND p.userId = u.id
       ) = 9
ORDER BY orders DESC
LIMIT 10

You should also rename your column orders to orderCount

one more thing that I would suggest is to take it all out of the WHERE clause, this is slow

do it like this

SELECT u.id, 
       u.avatar, 
       u.storeName,
       (SELECT COUNT(o.id) FROM orders o WHERE o.storeId = u.id) AS orderCount
FROM users u INNER JOIN products p ON u.id = p.userId 
WHERE p.categoryID = 9
ORDER BY orderCount DESC
LIMIT 10

StackExchange Code Review Q#57285, answer score: 3