Is it possible to sort by number of records in mysql?

Question

PHP

Hello, please tell me, is it possible to sort the data at the request level?
A query like this: SELECT * FROM category LEFT JOIN product ON category.id = product.category_id
WHERE category.id IN (1,2,3...)
product3..., category2 -> product1, product2...". But the categories should be sorted by the number of products in them, that is, the category with the most products is the first, with the fewest - the last

Answer 1

I think that you can pervert and cram it into one request, but this is absolutely not correct in terms of performance.
That's right - cache the number of products in a category in a separate field.
Update this field when the product category changes.
The request is simple and easy.
PS Don't use an asterisk when you're querying multiple tables, this is potentially a source of a lot of problems.

Answer 2

order by category

Answer 3

Yes it is possible.
The request should look like this:

SELECT category.name, GROUP_CONCAT(product.name)
FROM category
LEFT JOIN product ON category.id = product.category_id
GROUP BY category.id
ORDER BY COUNT(product.id) DESC

Answer 4

Make an additional collection through join, which will return the number by condition, and you will already sort by this value ...