The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

форумы  помощь  поиск  регистрация  майллист  вход/выход  слежка  RSS
"Сложные запросы из mysql"
Вариант для распечатки  
Пред. тема | След. тема 
Форумы Программирование под UNIX (MySQL)
Изначальное сообщение [ Отслеживать ]

"Сложные запросы из mysql"  +/
Сообщение от Alexander (??) on 29-Мрт-10, 02:38 
Всем привет.
Есть такие таблицы:
CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zip` int(11) DEFAULT NULL,
  `street` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `regions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text COLLATE utf8_unicode_ci,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `categories_companies` (
  `category_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Словами: есть компании. Каждая компания имеет один адрес. Каждый адрес имеет один город. Город входит в регион. Регион в страну. Каждая компания может входить в ноль или несколько категорий.

Задача такая. Есть 3 ситуации:
1. Имеются только id-шники категорий - нужно получить для них компании.
2. Имеются только id-шники: или города, или региона, или страны, или всех вместе, или в комбинации (например, 3 страны и 5 городов) - нужно получить для них компании
3.Имеются id-шники категорий и в разных вариациях стран, регионов и городов - нужно получить для них компании.

К сожалению, моих познаний в sql не хватает осилить такие запросы.

Спасибо

Высказать мнение | Ответить | Правка | Cообщить модератору

Оглавление

Сообщения по теме [Сортировка по времени | RSS]


1. "Сложные запросы из mysql"  +/
Сообщение от XAnder (ok) on 29-Мрт-10, 10:41 
>Словами: есть компании. Каждая компания имеет один адрес. Каждый адрес имеет один
>город. Город входит в регион. Регион в страну. Каждая компания может
>входить в ноль или несколько категорий.

Город не обязательно входит в регион (Москва, а?), кроме городов есть посёлки, станицы (побольше иных городов), аулы и т. п., так что схема не вполне адекватна. Это к слову, потому что часто вижу подобные недоделки на разных сайтах. Очень советую пересмотреть, пока вы только в начале пути - потом менять будет сложнее.

>1. Имеются только id-шники категорий - нужно получить для них компании.

Это совсем просто, есть же таблица categories_companies, которая буквально на этот вопрос и отвечает. Кстати, а где индексы?

>2. Имеются только id-шники: или города, или региона, или страны, или всех
>вместе, или в комбинации (например, 3 страны и 5 городов) -
>нужно получить для них компании

А здесь SELECT по нескольким таблицам с соответствующими условиями, связывающими эти таблицы по внешним ключам. Чуть сложнее, чем п. 1, но тоже довольно просто, только писанины побольше.

>3.Имеются id-шники категорий и в разных вариациях стран, регионов и городов -
>нужно получить для них компании.

Аналогично, только добавляется таблица-связка categories_companies и, как следствие, ещё немного чисто технической писанины.

>К сожалению, моих познаний в sql не хватает осилить такие запросы.

Действительно, "к сожалению" - запросы-то простые. Будет лучше, если вы самостоятельно их напишете, предварительно почитав документацию и/или какие-нибудь учебники/справочники по теме, иначе дальше будет совсем туго. Как только напишите что-то конкретное, выкладывайте тут - думаю, критики найдутся :-)

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

2. "Сложные запросы из mysql"  +/
Сообщение от Alexander (??) on 29-Мрт-10, 10:54 
>Город не обязательно входит в регион (Москва, а?), кроме городов есть посёлки,
>станицы (побольше иных городов), аулы и т. п., так что схема
>не вполне адекватна.

Москва входит в московскую область. А таблицу cities можно воспринимать как таблицу с населёнными пунктами, а не городами.


> Кстати, а где индексы?

Индексы не нужны - сайт написан на руби-рельсах. Собственно от этого и сложность - я ещё не делал сложных проектов, а рельсы вообще избавили от необходимости писать SQL...

> Как только напишите что-то конкретное, выкладывайте тут -
>думаю, критики найдутся :-)

Я как бы человек не ленивый. Для начала попытался сделать сам. Вам за "советы" спасибо...

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

3. "Сложные запросы из mysql"  +/
Сообщение от XAnder (ok) on 29-Мрт-10, 11:07 
>Москва входит в московскую область. А таблицу cities можно воспринимать как таблицу
>с населёнными пунктами, а не городами.

Москва не входит в Московскую область, Питер не входит в Ленинградскую, Севастополь не входит в АР Крым. Ну и далее в том же духе. В Википедии, например, можно об этом почитать.

>Индексы не нужны - сайт написан на руби-рельсах. Собственно от этого и
>сложность - я ещё не делал сложных проектов, а рельсы вообще
>избавили от необходимости писать SQL...

Тут я не советчик - с рельсами дел не имел.

>Я как бы человек не ленивый. Для начала попытался сделать сам. Вам
>за "советы" спасибо...

Пожалуйста. Вы бы лучше показали, что сделано, что не работает. Не может же быть, чтобы неленивый человек не смог сам написать простых, в общем-то, запросов.

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

4. "Сложные запросы из mysql"  +/
Сообщение от Alexander (??) on 29-Мрт-10, 11:42 
>Пожалуйста. Вы бы лучше показали, что сделано, что не работает. Не может
>же быть, чтобы неленивый человек не смог сам написать простых, в
>общем-то, запросов.

Я покажу, как у меня получилось. Но не rails, а на grails:

Company.findAll("from Company as b where (b.address.city.id  in (:cities) or b.address.city.country.id in (:countries) or b.address.city.region.id  in (:regions)", [countries:xxx, cities: yyy, regions: zzz])  - это для 2-го варианта

Company.findAll("from Company as b left join fetch b.categories as br where br.id in (:categories) and (b.address.city.id in (:cities) or b.address.city.country.id  in (:countries) or b.address.city.region.id in (:regions) group by b.id", [categories:categories_array, cities: cities_array, countries: ..., regions: ...] ) - это для 3-го случая


Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

5. "Сложные запросы из mysql"  +/
Сообщение от XAnder (ok) on 29-Мрт-10, 13:43 
>Company.findAll("from Company as b where (b.address.city.id  in (:cities) or b.address.city.country.id in (:countries) or b.address.city.region.id  in (:regions)", [countries:xxx, cities: yyy, regions: zzz]) - это для 2-го варианта

Хм... Интересно, конструкции типа "b.address.city.id" - это "рельсовое" расширение SQL? Я просто не в курсе, как уже говорил. Если нет, то тут ошибка, и даже несколько.

1. В предложении FROM должны быть перечислены все таблицы, по которым делается запрос. У вас только Company. Или всё-таки companies?

2. Это мы знаем, что b.address - ключ таблицы addresses, но SQL-движку это неизвестно, так что связи между таблицами нужно прописать явно. Например так:

  FROM companies, addresses, cities и т. д. WHERE companies.address_id=addresses.id AND addresses.city_id=cities.id AND и т. д.

Как я и говорил, много писанины. Или, возможно, будет лучше так:

  FROM companies LEFT JOIN addresses ON companies.address_id=addresses.id LEFT JOIN cities ON addresses.city_id=cities.id LEFT JOIN и т. д.

За подробностями и тонкостями отсылаю к "Справочному руководству по MySQL". Ищите про SELECT и JOIN.

3. Ну и далее на столбцы таблицы нужно ссылаться по имени этой таблицы, т. е. не "b.address.city.id", а "cities.id" и т. п.

4. А где, собственно, слово SELECT? Или "рельсы" его сами подставляют? Тогда где прописывается то, что должно быть на выходе запроса?

>Company.findAll("from Company as b left join fetch b.categories as br where br.id in (:categories) and (b.address.city.id in (:cities) or b.address.city.country.id  in (:countries) or b.address.city.region.id in (:regions) group by b.id", [categories:categories_array, cities: cities_array, countries: ..., regions: ...] ) - это для 3-го случая

Эх, нет времени здесь копаться, давайте сначала с предыдущим разберёмся, возможно и тут вопросы отпадут после этого.

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

6. "Сложные запросы из mysql"  +/
Сообщение от Alexander (??) on 29-Мрт-10, 13:56 
>Хм... Интересно, конструкции типа "b.address.city.id" - это "рельсовое" расширение SQL? Я просто
>не в курсе, как уже говорил. Если нет, то тут ошибка,
>и даже несколько.
>

Это HQL - Hibernate Query Language, который потом, разумеется, переводится в SQL при общении с базой.
HQL - язык, который позволяет описывать запросы на уровне объектов, а не на уровне табличек и строчек (поэтому можно делать "from Foo as foo where foo.bar.baz.name = 'Alexander'", то бишь разворачивать ассоциации; все необходимые join-ы Hibernate добавит сам).

Company.findAll("from Company as b, Address as a, Region as r where b.address=a.id and a.region=r.id and r.id in (:regions)", ... ) // вот это - не SQL.
Я не помню, как Hibernate именует таблички по-умолчанию, но в любом случае тут 'from Company as b' - это имя класса, а не имя таблицы. Звучит это "Выбрать объекты класса Company, объекты класса Address, ..., для которых выполнено ...". Как это Hibernate переведет в SQL заботить, по идее, не должно, потому что Hibernate 'just works'.

>1. В предложении FROM должны быть перечислены все таблицы, по которым делается
>запрос. У вас только Company. Или всё-таки companies?

Ответ выше.

Дело в том, чтобы переделать запрос из HQL в SQL, мне нужно понять как это работает. К сожалению, я не понимаю как работают join-ы - с чем их вообще едят. С остальным вроде как более-менее ясно.
В ruby-on-rails не работает то, что работает в grails.

Тем не менее, спасибо за участие. Буду курить доки :)

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

7. "Сложные запросы из mysql"  +/
Сообщение от XAnder (ok) on 29-Мрт-10, 14:18 
>Это HQL - Hibernate Query Language, который потом, разумеется, переводится в SQL
>при общении с базой.
>HQL - язык, который позволяет описывать запросы на уровне объектов, а не
>на уровне табличек и строчек

Понятно. Тогда я тут дальше даже пытаться подсказывать не буду :-)

>Дело в том, чтобы переделать запрос из HQL в SQL, мне нужно
>понять как это работает. К сожалению, я не понимаю как работают
>join-ы - с чем их вообще едят.

Чтобы было не так страшно, можно представить себе, что JOIN - это такая особенная запятая, к которой можно пристегнуть условие.

Успехов!

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

Архив | Удалить

Рекомендовать для помещения в FAQ | Индекс форумов | Темы | Пред. тема | След. тема




Спонсоры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2023 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру