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.
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