Tables and raw query

raw_query.sql

select `commodities`.`name` as `commodity_name`, `price_types`.`name` as `type_name`, `measure_units`.`name` as `unit_name`, `global_admins`.`admin0_code` as `admin0`, `global_admins`.`admin0_name` as `country_name`, `global_admins`.`admin1_code` as `admin1`, `global_admins`.`admin1_name` as `governorate_name`, ROUND(AVG(CMP.price) / 1000, 3) as price from `commodities_monthly_prices` as `CMP` inner join `locations` on `CMP`.`location_id` = `locations`.`id` inner join `global_admins` on `locations`.`admin0` = `global_admins`.`admin0_code` inner join `commodities` on `CMP`.`commodity_id` = `commodities`.`id` inner join `price_types` on `CMP`.`price_type_id` = `price_types`.`id` inner join `measure_units` on `CMP`.`measure_unit_id` = `measure_units`.`id` where `locations`.`admin0` = ? group by `CMP`.`price_year`, `CMP`.`price_month`, `commodity_name`, `type_name`, `unit_name`, `global_admins`.`admin0_name`, `global_admins`.`admin0_code`, `global_admins`.`admin1_name`, `global_admins`.`admin1_code` order by `commodity_name` asc

tables.sql

CREATE TABLE `commodities_monthly_prices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `price` double NOT NULL DEFAULT '0',
  `price_month` int(10) unsigned DEFAULT NULL,
  `price_year` int(10) unsigned DEFAULT NULL,
  `source` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'commodity source: wfp, and other ngo',
  `un_code` int(10) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `currency_id` bigint(20) unsigned DEFAULT NULL,
  `market_id` bigint(20) unsigned DEFAULT NULL,
  `measure_unit_id` bigint(20) unsigned DEFAULT NULL,
  `period_id` bigint(20) unsigned DEFAULT NULL,
  `price_type_id` bigint(20) unsigned DEFAULT NULL,
  `commodity_id` bigint(20) unsigned DEFAULT NULL,
  `data_source_id` bigint(20) unsigned DEFAULT NULL,
  `location_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `commodities_monthly_prices_price_index` (`price`),
  KEY `commodities_monthly_prices_price_month_index` (`price_month`),
  KEY `commodities_monthly_prices_price_year_index` (`price_year`),
  KEY `commodities_monthly_prices_source_index` (`source`),
  KEY `commodities_monthly_prices_un_code_index` (`un_code`),
  KEY `commodities_monthly_prices_data_source_id_foreign` (`data_source_id`),
  CONSTRAINT `commodities_monthly_prices_data_source_id_foreign` FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=60742 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `admin0` int(10) unsigned NOT NULL,
  `admin1` int(10) unsigned DEFAULT NULL,
  `admin2` int(10) unsigned DEFAULT NULL,
  `admin3` int(10) unsigned DEFAULT NULL,
  `admin4` int(10) unsigned DEFAULT NULL,
  `admin5` int(10) unsigned DEFAULT NULL,
  `data_source_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `locations_data_source_id_foreign` (`data_source_id`),
  KEY `locations_admin1_index` (`admin1`),
  KEY `locations_admin2_index` (`admin2`),
  KEY `locations_admin3_index` (`admin3`),
  KEY `locations_admin4_index` (`admin4`),
  KEY `locations_admin5_index` (`admin5`),
  CONSTRAINT `locations_data_source_id_foreign` FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=57244 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `global_admins` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `continent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `region` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `un_code` bigint(20) DEFAULT NULL,
  `iso3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `wfp_iso3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin0_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin0_code` bigint(20) DEFAULT NULL,
  `admin1_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin1_code` bigint(20) DEFAULT NULL,
  `admin2_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin2_code` bigint(20) DEFAULT NULL,
  `admin3_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin3_code` bigint(20) DEFAULT NULL,
  `admin4_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin4_code` bigint(20) DEFAULT NULL,
  `admin5_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin5_code` bigint(20) DEFAULT NULL,
  `salb0` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `salb1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `salb2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `str_year0` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `str_year1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `str_year2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `str_year3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `str_year4` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `str_year5` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exp_year0` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exp_year1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exp_year2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exp_year3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exp_year4` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `exp_year5` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_updat` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=58419 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `commodities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=474 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `price_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `measure_units` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci