MySQL n results per GROUP BY, quantity order

Question

Country- Companyhave a 1-n relationship.

Company- Dishare related to nn.

I need to get dishes from each locale, sorted in descending order, based on their calculation in the corresponding language attribute, and each language standard has 100 results.

I still 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 conclusion:

How can i achieve this?

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 sub request, counting 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 count the number of dishes 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 subroutine:

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}