MySQL n results per GROUP BY, Order by count

Question

Country - Company have a 1-n relationship.

Company - Dish have a n-n relationship.

I need to get the Dishes from each locale ordered descending based of their count in the respective locale, each locale having 100 results.

So far I have

SELECT dishes.title,
       dishes.name,
       dishes.id,
       countries.locale
FROM countries 
    JOIN companies ON companies.`country_id` = countries.`id`
    JOIN company_dish  ON company_dish.`company_id` = companies.`id`
    JOIN dishes  ON company_dish.`dish_id` = dishes.`id`
GROUP BY dishes.`title`, countries.locale
ORDER BY ??? count_dishes_in_each_locale ?? DESC

Sample data:

Desired output:

How can I achieve that?

Answer 1

Try with sub request...

Your request :

SELECT
    dishes.title,
    dishes.name,
    dishes.id,
    countries.locale,
    countries_count.dishes_nb
FROM countries 
    JOIN companies ON companies.`country_id` = countries.`id`
        JOIN company_dish ON company_dish.`company_id` = companies.`id`
            JOIN dishes ON company_dish.`dish_id` = dishes.`id`
    JOIN
        ({SubRequest}) countries_count ON countries_count.`country_id` = countries.`id`
ORDER BY
    countries_count.dishes_nb DESC

and the sub request, count dishes by country :

SELECT
    countries.id AS country_id,
    COUNT(company_dish.dish_id) AS country_dishes_nb
FROM countries 
    JOIN companies ON companies.country_id = countries.id
        JOIN company_dish ON company_dish.company_id = companies.id
GROUP BY
    country_id

or to count for each dish by country :

Your request :

SELECT
    dishes.title,
    dishes.name,
    dishes.id,
    countries.locale,
    countries_count.dishes_nb
FROM countries 
    JOIN companies ON companies.`country_id` = countries.`id`
        JOIN company_dish ON company_dish.`company_id` = companies.`id`
            JOIN dishes ON company_dish.`dish_id` = dishes.`id`
    JOIN
        ({SubRequest}) countries_count ON countries_count.`country_id` = countries.`id`
        AND countries_count.`dish_id` = dishes.`id`
ORDER BY
    countries_count.dishes_nb DESC

and the sub request :

SELECT
    countries.id AS country_id,
    dishes.id AS dish_id,
    COUNT(company_dish.dish_id) AS country_dishes_nb
FROM countries 
    JOIN companies ON companies.country_id = countries.id
        JOIN company_dish ON company_dish.company_id = companies.id
            JOIN dishes ON company_dish.dish_id = dishes.id) AS countries_count ON countries_count.country_id` = countries.`id`
GROUP BY
    country_id, dish_id

And concat(replace) in {SubRequest}