SQL Select unique categories sorted by popularity

Question

I am trying to get a list of categories that are in use and then sort them based on the number of records to be able to assemble a list of categories and use in the site filter. So they must follow these restrictions:

• Unique categories;
• Categories that are used;
• Sort by popularity (the more records it has, the higher it should get);

For this I have 2 tables, 1 with the list of products and categories:

relacao_categoria
id |  id_categoria | id_produto

And the other with the list of categories

cadastro_categoria
id  | nome_categoria

At the moment I just could not select based on popularity, because later I need to sort according to the number of records found in the table cadastro_produto

This is my sql:

$qry = sql("SELECT DISTINCT id_categoria FROM relacao_categoria");

// Processo para montar a array de id's com o resultado

$qry = sql("SELECT id, nome_categoria FROM cadastro_categoria WHERE id IN ($ids)");

How can I do so that when getting the 2nd sql results, they are in order of popularity?

Answer 1

The suggestion of the Reginaldo Rigo comment is a good solution. Optionally you can remove the first sql query sql("SELECT DISTINCT id_categoria FROM relacao_categoria"); and make a Join Inner in the second query.

Using the SQL editor of W3C , the query would look like this (tested with two tables with different names, Categories and products, but that fit your situation):

SELECT count(*) as popularidade, Categories.CategoryID, CategoryName 
from Categories, Products 
WHERE Categories.CategoryID = Products.CategoryID group by
Categories.CategoryID, CategoryName order by count(*) desc;