Возможно ли оптимизировать mysql запрос?

Question

[Vasyl Vandych]

SELECT 
      name,iso2,(SELECT COUNT(`link_id`) FROM `links` WHERE `country` = `countries`.`iso2`) AS `total_links` 
FROM 
           `countries` 
WHERE (SELECT COUNT(`link_id`) FROM `links` WHERE `country` = `countries`.`iso2`) > 0 ORDER BY `name`

Вывод мне нужен вот такой:

[{"name":"United States","iso2":"US","total_links":"1"},{"name":"Venezuela","iso2":"VE","total_links":"1"},{"name":"Vietnam","iso2":"VN","total_links":"2"}]

Время выполнения - 1.422147 s.
Можно ли как-то оптимизировать?

Comments

[Adamos]

Про JOIN / GROUP BY / HAVING почитайте.

[Vasyl Vandych]

Adamos, помогите составить запрос, а я по примеру обучусь. Спасибо

[Adamos]

Vasyl Vandych, ну вот между этими тремя операторами вставьте свои таблицы, поля, по которым они ключуются, и условие отбора. Должно получиться, если хочется обучиться.

Answer 1

[Slava Rozhnev]

Сразу готовый JSON из базы данных:

WITH countries_links AS (
    SELECT JSON_OBJECT('name', `name`, 'iso2', `iso2`, 'links', COUNT(`link_id`)) d
    FROM `links`
    JOIN `countries` ON `countries`.`iso2` = `links`.`country`
    GROUP BY `name`, `iso2`
    HAVING COUNT(`link_id`) > 0
) SELECT JSON_ARRAYAGG(d) FROM countries_links

Пример: https://sqlize.online/sql/mysql80/087bf8ba4f587b13...

Answer 2

[Rsa97]

SELECT `c`.`name`, `c`.`iso2`, `t`.`total_links`
  FROM (
    SELECT `country`, COUNT(*) AS `total_links`
      FROM `links`
      GROUP BY `country`
  ) AS `t`
  JOIN `countries` AS `c` ON `c`.`iso2` = `t`.`country`
  ORDER BY `c`.`name`

Comments

[Vasyl Vandych]

при таком запросе у меня ошибка
Fatal error: Uncaught Error: Call to a member function fetch_object() on bool

[Vasyl Vandych]

все решил, вы пропустили iso2

Answer 3

[Vasyl Vandych]

Дополню еще этот вопрос, чтобы не плодить темы.
Благодоря ответам выше Rsa97 я вывел 3 блока таким образом:

$country_query = database()->query("
			SELECT `c`.`name`, `c`.`iso2`, `c`.`subregion`, `t`.`total_links`
  FROM (
    SELECT `country`, COUNT(*) AS `total_links`
      FROM `links`
      GROUP BY `country`
  ) AS `t`
  JOIN `countries` AS `c` ON `c`.`iso2` = `t`.`country`
  ORDER BY `t`.`total_links` DESC
		");

		$state_query = database()->query("
			SELECT `s`.`name`, `s`.`id`, `s`.`country_code`, `t`.`total_links`
  FROM (
    SELECT `state`, COUNT(*) AS `total_links`
      FROM `links`
      GROUP BY `state`
  ) AS `t`
  JOIN `states` AS `s` ON `s`.`id` = `t`.`state`
  ORDER BY `t`.`total_links` DESC
		");

		$city_query = database()->query("
			SELECT `c`.`name`, `c`.`id`, `c`.`country_code`, `t`.`total_links`
  FROM (
    SELECT `city_name`, COUNT(*) AS `total_links`
      FROM `links`
      GROUP BY `city_name`
  ) AS `t`
  JOIN `cities` AS `c` ON `c`.`id` = `t`.`city_name`
  ORDER BY `t`.`total_links` DESC
		");

Как мне вывести в 3м блоке под именем Города его регион, но регион в таблице cities записан таким образом:



возможно ли вывести индекс как имя а не цифру?