Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
Есть таблица в которой хранятся древовидные списки :

id(key)CaptionParent
1ToyotaNULL
2NissanNULL
3FordNULL
4Ractis1
5Yaris1
6Versa1
7Almera2
8Tiana2
9Focus3
10Kuga3
11Wagon3
12Disel11
13Benz11
14Gaz11


В дереве имеет вид :

Caption ID
Toyota 1
Ractis 4
Yaris 5
Versa 6
Nissan 2
Almera 7
Tiana 8
Ford 3
Focus 9
Kuga 10
Wagon 11
Dizel 12
Benz 13
Gaz 14
Список может иметь не ограниченное количество вложений, самое главное что у верхушки Parent=NULL

Мне нужно зная ID любого элемента получить список его и его родителей (ветку)

Например ID=12 доложен вернуть результат

IDCaption
12Dizel
11Wagon
3Ford



А ID=8 должен вернуть результат :
IDCaption
8Tiana
2Nissan



Табличка с машинами взята для примера :) вообще делаю систему прав доступа ...

На текущий момент есть скрипт на PHP который справляется с данной задачей, но он генерирует огромное количество запросов, и при частом вызове просто делает лишние телодвижения :(

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

Заранее благодарен ...
1 сен 14, 10:15    [16518870]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Fastereus
с чего начать
Например, с изменения структуры хранения данных.
Можно перейти на nested set. А можно просто добавить поле, где хранить материализованный путь.
1 сен 14, 15:28    [16520192]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
alex564657498765453
Member

Откуда:
Сообщений: 1925
элементарно ватсон

ЗЫ
NS-Tree
1 сен 14, 15:36    [16520223]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
Нашел лишь такой ответ на хабре
автор
Вынужден огорчить пользователей mySQL – в этой СУБД придется рекурсию организовывать внешними по отношению к СУБД средствами, например на php (код приводить не буду, его публиковали не раз, например здесь, достаточно поискать по запросу “mySQL tree” и т.п.).
2 сен 14, 02:37    [16521871]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
Akina,

Менять не чего нельзя ...
2 сен 14, 02:39    [16521872]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
tanglir
Member

Откуда:
Сообщений: 28966
Fastereus
Нашел лишь такой ответ на хабре
автор
Вынужден огорчить пользователей mySQL – в этой СУБД придется рекурсию организовывать внешними по отношению к СУБД средствами, например на php (код приводить не буду, его публиковали не раз, например здесь, достаточно поискать по запросу “mySQL tree” и т.п.).
Не читайте перед обедом советских газетхабра.
Хотя рекурсивных запросов и в самом деле нет, вполне можно обойтись "внутренними" средствами - хранимыми процедурами.
А если у вас не планируется перенос веток, то материализованный путь имхо будет лучшим решением.
2 сен 14, 06:35    [16521918]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
tanglir
Member

Откуда:
Сообщений: 28966
Fastereus
Менять не чего нельзя
нельзя менять структуру или вообще ничего нельзя? т.е. даже хранимку свою добавить тоже нельзя?
2 сен 14, 06:36    [16521919]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
Сервак чуджой, доступ к процедурам не проверил .. но в принципе я не бумаю что юзание процедур сократит количество запросов, а получется тот же гемор что я на PHP нарисовал только в SQL... так что наверно вопрос снят, я просто не знал что нет рекурсии в муське, очень жаль ...
2 сен 14, 16:51    [16524777]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
tanglir
Member

Откуда:
Сообщений: 28966
Fastereus
а получется тот же гемор что я на PHP нарисовал только в SQL
Только в варианте с ХП это будет один запрос, который можно использовать везде а в пхп-варианте это будет N запросов в цикле, которые каждый раз придётся копипастить.
3 сен 14, 05:22    [16526654]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
Следующее было накидано налету, протестировано, провда не оптимизировано но на мой взгляд все весьма локонично вышло :
	/*
		Загружает и формирует массив прав по умолчанию

		=false в случае ошибки, или массив прав
	*/
	private function GetDefaultRights($level=arTop)
	{
		$Base=$this->Provider();//Создаём новый доступ к базе данных , потому как функция реляционная
		if($level==arTop) $lvl=' IS NULL';
			else $lvl="='".$level."'";
		//Получаем все права
		$Request="SELECT * FROM `".$this->TablePrefix."rights` WHERE `Parent`$lvl ORDER BY `ID`;";
		if(!$Base->Query($Request) || !$Base->NumRows() && $level==arTop)
		{
			$this->Error(__FUNCTION__,'0x100D',array($Base->GetError()));
			return false;
		}

		//Пустой массив
		$result=array();

		//Добавляем все элементы
		while($row=$Base->Row())
		{
			$access=$this->RightFromDB($row['Default']);//конвертируем представление базыданных в внутреннее.

			//Обрабатываем вложенные права
			$items=$this->GetDefaultRights($row['ID']);
			if(count($items))
			{
            	$result[$row['Caption']]=array("access"=>$access,"items"=>$items);
			}
			else
			{
				//Нет вложенных политик
				$result[$row['Caption']]=$access;
			}
		}
		return $result;
	}


В PHP нет проблем с рекурсией, ну и есть такая же работающая в обратном направлении принцип тот же
3 сен 14, 14:30    [16529419]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
alex564657498765453
Member

Откуда:
Сообщений: 1925
tanglir
Fastereus
Нашел лишь такой ответ на хабре
пропущено...
Не читайте перед обедом советских газетхабра.
Хотя рекурсивных запросов и в самом деле нет, вполне можно обойтись "внутренними" средствами - хранимыми процедурами.
А если у вас не планируется перенос веток, то материализованный путь имхо будет лучшим решением.


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

другое дело, если ТС будет организовывать обход дерева, тут бы сразу задуматься о памяти. обход надо организовывать обходя в глубь а не по горизонтали.
3 сен 14, 15:15    [16529815]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
Считаю что не важно как лиж бы работало, в моем случае сначало нужно написать около 25 классов а потом уже их оптимизировать, тем более чтопоследнее делается практически до бесконечности, про СУБД я и в правду не ГУРУ, но как видно это мой первый вопрос был и увы ответа на него нет ...
4 сен 14, 03:26    [16532780]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
tanglir
Member

Откуда:
Сообщений: 28966
Fastereus
В PHP нет проблем с рекурсией
а в мускле есть?
Fastereus
это мой первый вопрос был и увы ответа на него нет
"нет"? простите, а что вы подразумеваете под "ответом"? неужели готовое решение?
4 сен 14, 05:07    [16532797]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
alex564657498765453
Member

Откуда:
Сообщений: 1925
Fastereus
Считаю что не важно как лиж бы работало, в моем случае сначало нужно написать около 25 классов а потом уже их оптимизировать, тем более чтопоследнее делается практически до бесконечности, про СУБД я и в правду не ГУРУ, но как видно это мой первый вопрос был и увы ответа на него нет ...


тебе же написали - NS tree способ хранения дерева, оптимимальный при редких изменениях и частых выборках по типу - получить всех детей, получить всех родителей, получить всех братьев...итд
4 сен 14, 12:36    [16534272]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
NS tree = SAXX (если кто знает что называли так....)

tanglir
Fastereus
В PHP нет проблем с рекурсией
а в мускле есть?
Fastereus
это мой первый вопрос был и увы ответа на него нет
"нет"? простите, а что вы подразумеваете под "ответом"? неужели готовое решение?


Думаю нет ...
Fastereus
Нашел лишь такой ответ на хабре
автор
Вынужден огорчить пользователей mySQL – в этой СУБД придется рекурсию организовывать внешними по отношению к СУБД средствами, например на php (код приводить не буду, его публиковали не раз, например здесь, достаточно поискать по запросу “mySQL tree” и т.п.).



И да я бы хотел готовый ответ, ибо я например если отвечаю даю готовые ответы ... простите если не оправдал надежд ...
5 сен 14, 11:58    [16539119]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
tanglir
Member

Откуда:
Сообщений: 28966
Fastereus,

mysql Recursive limit 255 (as set by the max_sp_recursion_depth variable) was exceeded for

Там, правда, от родителей к потомкам, но если вы не сумеете развернуть в обратную сторону, то какой смысл вообще что-то советовать?
Кстати, если дерево не очень глубокое, то и вариант ТСа(явно рекурсивный) вполне работоспособен.
5 сен 14, 12:11    [16539275]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
bochkov
Member

Откуда: Камчатка
Сообщений: 4069
при условии что у детки только один парент
SELECT @id as child_id,@id:=(SELECT Parent FROM table_p WHERE id=@id) as parent_id
FROM table_p,(SELECT @id:=12) as init
6 сен 14, 11:59    [16543857]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
bochkov
Member

Откуда: Камчатка
Сообщений: 4069
bochkov
при условии что у детки только один парент
SELECT @id as child_id,@id:=(SELECT Parent FROM table_p WHERE id=@id) as parent_id
FROM table_p,(SELECT @id:=12) as init

маленько поправлю
SELECT @id as child_id,@id:=(SELECT Parent FROM table_p WHERE id=@id LIMIT 1) as parent_id
FROM table_p,(SELECT @id:=12) as init
6 сен 14, 12:44    [16543913]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
bochkov
bochkov
при условии что у детки только один парент
SELECT @id as child_id,@id:=(SELECT Parent FROM table_p WHERE id=@id) as parent_id
FROM table_p,(SELECT @id:=12) as init

маленько поправлю
SELECT @id as child_id,@id:=(SELECT Parent FROM table_p WHERE id=@id LIMIT 1) as parent_id
FROM table_p,(SELECT @id:=12) as init

Нет деток во первых много во вторых, вложенность хаотична по веткам , в одних 1 или две в других 8 или девять итераций ...

Ваш метод не прокатит, собственно я пришел к мнению что пока оставлю на ПХП функцию ... тем более что у меня система это делает один раз при входе пользователя и лишняя секунда не важна ...
6 сен 14, 18:36    [16544396]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
bochkov
Member

Откуда: Камчатка
Сообщений: 4069
Fastereus,
в этом запросе нет ограничений на вложенность,
хоть бы попробовал
6 сен 14, 23:47    [16545199]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
Я конечно попробую ... но только когда приеду с всеросийской стройки счас на Космодроме восточный, тут дела немного другие ...


Я не понял запроса увидел некторые не знакомые конструкции, попробую обязательно а еще до этого прочитаю как этодолжно работать , яж сказал что не БАЗИСТ йа ...
7 сен 14, 15:58    [16546084]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
tanglir
Member

Откуда:
Сообщений: 28966
Fastereus
деток во первых много
bochkov писал о родителе, а не о детях
а запрос - неочевидный, да
из серии "обводим мускль вокруг пальца" :)
8 сен 14, 06:27    [16547723]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
tanglir
Fastereus
деток во первых много
bochkov писал о родителе, а не о детях
а запрос - неочевидный, да
из серии "обводим мускль вокруг пальца" :)

Еще раз уточну что базы данных не когда небыли моим коньком , вот страшные скалярные строкина CGI не вопрос а СУБД всегда мло сталкивались со мной, я приеду через 2 недели попробую сначала понять потом сделать ...
9 сен 14, 07:36    [16552593]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи в составлении хитрого запроса  [new]
userlive
Member

Откуда:
Сообщений: 4
Почитайте MySQL. Иерархические запросы.
9 сен 14, 23:21    [16556953]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Прошу помощи в составлении хитрого запроса  [new]
Fastereus
Member

Откуда:
Сообщений: 31
bochkov, приветствую , хоть и куча лет прошла но тема опять всплыла в новь ... попробовал и вот результат :


child_id;parent_id;
493;492;
492;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;
NULL;NULL;


Как видишь не работает :)

А вот так работает :
SET @child_id = '869';
SELECT @child_id AS `ID` , @child_id := (SELECT Owner FROM test_th_nodes WHERE `ID` = @child_id LIMIT 1) AS `Owner` FROM test_th_nodes WHERE @child_id IS NOT NULL;


ID;Owner;
869;867;
867;NULL;


Сообщение было отредактировано: 31 янв 20, 06:49
31 янв 20, 06:47    [22070101]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / MySQL Ответить