chelkaz
@chelkaz

Как правильно хранить и выбирать города в Базе Данных?

Есть три таблицы loc_name и location и type
В loc_name два поля NAME и LOC_ID
Пример:
-----NAME------|----LOC_ID---|---
----Москва-----|-----21--------|---
----Мос. Обл---|-----22--------|---
--Дудино село-|-----29--------|---
И так далее... В ней хранится все, и города и деревни и области и районы...
В другой таблице location Есть поля:
PARENT_ID и TYPE_ID и ID
И в таблице type хранятся типы (Город, Область, Село, Район)
Сейчас я делаю так, у меня связаны таблица loc_name с location
Например человек ищет Дудино, тогда я выбираю все схожие из loc_name
И смотрю, если их тип город или село то забираю. Но мне нужны же полные их данные, поэтому я массив с нужными городами и селами прогоняю через рекурсивную функцию, что бы из таблицы loc_name с помощью связи с таблицей location найти районы и прочее, до последнего родителя.
Все работает правильно, то например если я делаю поиск и результат из 30, то запросов около 200 получается, ведь например я ввел Мос И соответственно начинается выборка %Мос%
Как вообще правильно организовать такое хранение? Я думал может создать просто одну таблицу где все уже будет разложено? Типа Название Регион Район и т. д. Тогда рекурсию делать не нужно, но тогда будет одна здоровая таблица и тяжелая.
Вот как сейчас я делаю:
// Тут сам запрос
$city = strip_tags($request->input('city'));
//Здесь я проверяю в связанной таблице на тип, и если это город или село то выбераю
        $loc_id = LocName::where('NAME', 'like', '%'.$city.'%')->with(['location' => function ($query)
        {
            $query->where('TYPE_ID', '>', '4');
        }])->take(50)->orderBy('NAME', 'desc')->get();

        $all = array();

// Эта функция рекурсивно ищет полное название
// например: Дудино, Щелковский район, Московская область, Россия
        function get_parent($id, $path)
        {
// Получаю название на русском и сразу со связью что бы проверить тип
//и если это еще не Страна (страна это главные родитель) то добавляем название в массив и ищем далее
            $par_ar = LocName::where('LANGUAGE_ID', 'ru')->with('location')->where('LOCATION_ID', $id)->first();
            $path[] = $par_ar->NAME;
            if($par_ar->location->TYPE_ID > 1)
            {
                return get_parent($par_ar->location->PARENT_ID, $path);
            }
// Если это страна то отдаем готовый массив для одного города или села.
            return $path;
        }

        foreach ($loc_id as $id)
        {
            $id = $id->LOCATION_ID;
// тут собираем все в один
            $all[$id] = get_parent($id, $path = array());
        }
// Ну и тут я готовый массив <b>$all</b> с городами и селами уже во вьюху кидаю.

Массивы такого вида как и нужно все работает:
6034 => array [
    0 => "Швейник-ПШО Москва снт"
    1 => "Котово деревня"
    2 => "Наро-Фоминский район"
    3 => "Московская область"
    4 => "Россия"
  ]
  8330 => array [
    0 => "Сигнал 74 км ш.Москва-Нижний Новгород снт"
    1 => "Кузнецы деревня"
    2 => "Павлово-Посадский район"
    3 => "Московская область"
    4 => "Россия"
  ]

Но вот запросов просто немерено: Вот дебагер показывает:
select * from `loc_name` where `NAME` like '%москва%' order by `NAME` desc limit 50
1.76s
select * from `location` where `location`.`ID` in ('19', '6034', '6454', '7297', '8004', '8008', '8176', '8330', '10291', '10294', '11162', '13486', '55020', '55336', '71509', '72732', '110555', '140372', '148382', '169991', '214864') and `TYPE_ID` > '4'
1.97ms
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '6034' limit 1
990μs
select * from `location` where `location`.`ID` in ('6034')
670μs
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '6030' limit 1
870μs
select * from `location` where `location`.`ID` in ('6030')
740μs
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '5939' limit 1
960μs
select * from `location` where `location`.`ID` in ('5939')
750μs
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '2' limit 1
730μs
select * from `location` where `location`.`ID` in ('2')
И так далее... Еще где то 170 запросов...

И Таких запросов около 200!!!
Как сделать правильно?
  • Вопрос задан
  • 1145 просмотров
Пригласить эксперта
Ответы на вопрос 2
InstantMedia
@InstantMedia
CREATE TABLE `geo_cities` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int(10) unsigned NOT NULL DEFAULT '0',
  `region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  `ordering` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `country_id` (`country_id`),
  KEY `region_id` (`region_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `geo_regions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(64) NOT NULL DEFAULT '',
  `ordering` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `geo_countries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `alpha2` char(2) NOT NULL DEFAULT '',
  `alpha3` char(3) NOT NULL DEFAULT '',
  `iso` int(11) NOT NULL DEFAULT '0',
  `ordering` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `alpha2` (`alpha2`),
  KEY `alpha3` (`alpha3`),
  KEY `iso` (`iso`),
  KEY `ordering` (`ordering`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


Далее связанные селекты (HTML тег select):
первый селект - список стран;
выбрали страну - подгрузили список регионов;
выбрали регион - подгрузили список городов;
выбрали город.

Для удобного выбора в селектах можно использовать например это.

Для быстрого определения региона -> страны по городу (по его полному названию) делать выборку из таблицы geo_cities, соединяя её с остальными - связующие поля есть.
Ответ написан
Rsa97
@Rsa97
Для правильного вопроса надо знать половину ответа
Поменять структуру хранения на Nested Set, тогда всех родителей можно будет выбрать одним запросом.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы