Jak najoptymalniej wykonac zapytanie SQL Opcje

[deirathe]

Powiedzmy ze mam tabele kategorie a w niej pola id i parent
id|parent
1 |0
2 |1
3 |1
4 |2
5 |4
6 |4
7 |5

no i powiedzmy ze pobralem element o id 7, jak najlepiej wykonac zapytanie zeby zwrocilo mi wszystkich rodzicow w poprawnej kolejnosci, tj

5,4,2,1

[Indeo]

To ciekawe zagadnienie topologiczne i przykład jak w małej prostej tabeli można zapisać dowolnie złożona strukturę powiązań typu - potomek-rodzic. Wbrew pozorom (jak dla mnie) wydobycie takiej sekwencji nie jest proste. Na myśl przychodzi analogia do przetwarzania dokumentów XML. Wg mnie jednym zapytaniem SQL tego nie zrobisz. Możesz napisać zapytanie, które dojdzie do pewnego poziomu, ale będzie to z góry założona liczba poziomów.
Musisz przejść przez wszystkie poziomy rekurencyjnie.
Najprościej chyba napisać pętle w php, która będzie wybierać dla danego potomka jego rodzica, w następnym przebiegu rodzica z poprzedniej pętli będzie traktować jako potomka, pobierze dla niego rodzica i tak w kółko tak długo aż zostanie spełniony warunek dotarcia "na powierzchnię" czyli parent = 0 kiedy to pętla zakończy działanie a przechwycone do pomocniczej zmiennej numery rodziców można złozyć w ostateczną "ścieżkę genealogiczną".
Mozliwe też, że cos takiego mozna zapisać w postaci procedury w samym SQL'u. Tam też można składać pętle.

[SirZooro]

Poczytaj te artykuły:
Managing Hierarchical Data in MySQL
Storing Hierarchical Data in a Database

[deirathe]

No wlasnie to drugie rozwiazanie jest malo optymalne bo mnoza mi sie zapytania, czego chce uniknac a pierwszy artykul nadal czytam, ale juz go chyba kiedys widzialem tylko nie mialem na to czasu, a co do pierwszego to albo musze znac ilosc zaglebien i sa one ustalone albo mam numerowanie w taki sposb zeby pobierac zakres, z tym ze to wiaze sie wtedy z duza iloscia zapytan podczas tworzenia nowej podkategorii, bo trzeba aktualizowac wiekszosc wierszy :/

Ten post edytował deirathe 23.01.2008, 17:17:18

[Indeo]

W takim razie może być jeszcze prościej. Zastanówmy sie praktycznie: skąd biorą sie kolejne wpisy w takiej hierarchicznej strukturze? Prawdopodobnie ktoś dodawał kolejne gałęzie dysponując w danym momencie wiedzą o "rodzicach" pozycji, którą właśnie tworzy (nic nie bierze sie z nikąd (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) . Zatem można w tej tabeli dodać kolejne pole o nazwie path w którym zapisywalibyśmy kolejno ścieżki genealogiczne kolejnych elementów. Przykładem są systemy plików - po katalogach tez trzeba chodzić rekurencyjnie ale tabela alokacji plików na danej partycji ma gotowe powiązania co jest gdzie. Podobną rolę spełniałoby to pole w tabeli. Może wygląda to dziwnie i nieelegancko ale zauważ, że nawet gdyby trzeba było przed dodaniem każdej kolejnej pozycji w strukturze odpytać "historię rodziców" to to odpytanie odbywałoby się tylko raz dla każdego elementu! Taka ściąga (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg) Rzeczywiste bazy danych są czasem dalekie od tych akademickich, ale dzięki temu czasem są szybsze.

[deirathe]

to jest dość dobry pomysł, ale co w wypadku gdy aktualizuje rodzica? Chyba że ścieżke zapisywać jako idgrampa/idparent/idchild , tylko wtedy jeżeli chcę poznać ich wartości musiałbym wykonywać tyle zapytań ile mam w ścieżce rodzicow dziadkow i innych przodkow

[bim2]

Można spróbować pobrać to do tablicy $array[$rodzic] = $id; i rekurencją przeskakiwać to (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[deirathe]

czyli pobrać całą tabelę do tablicy?

[bim2]

Tak, dziś podrzucę kod, ale teraz jest za wcześnie rano (IMG:http://forum.php.pl/style_emoticons/default/biggrin.gif)

[Indeo]

To proste w przypadku zmiany musisz mieć skrypt, który "zaktualizuje" zapisane relacje.
Lepiej raz na kilka dni odpalać mocny skrypt niż 100 000 razy inny, 100 razy szybszy, który w rzeczywistości pożre 100 000 / 100 = 1000 razy więcej zasobów.
Najlepiej aktualizować tylko od modyfikowanego "rodzica" w dół (nie trzeba wszystkiego). Będzie to wykonywane jednorazowo (przy każdej zmianie na szczeblu rodzica). Poza tym zdarzeniem wyciągnięcie relacji nie będzie wymagało ani dodatkowych pytań ani operacji w php. Można tez napisać trigger, który sam będzie pilnował aktualizacji elementów potomnych na podstawie modyfikowanego rodzica i to samoczynnie, kaskadowo w dół aż osiągnie rekordy "najmłodsze" i zakończy działanie. Myślę, że to jest wykonalne, po prostu trigger byłby przy jednej zmianie kaskadowo wyzwalany z góry na dół. To chyba najbezpieczniejszy sposób ochrony integralności danych (w połączniu z kluczami obcymi). Przy śmiganiu z danymi raz w mysql a raz w php, można coś pogubić przy wystąpieniu błędu (chyba, że sie użyje transakcji, a transakcje jak wiadomo, wymagają tabel Innodb i spowalniają prace mysql)

[deirathe]

"chili", robimy sobie tabele:
id|path|name
gdzie 'path' to powiedzmy nasza sciezka skladajaca sie z #id:array{}/#id:array{}, i podczas aktualizacji rekordu pobieramy inne ktore w polu path posiadaja wyrazenie #id obecnie aktualizowanego rekordu i je aktualizujemy, czy lepiej "rąbnąć" to cronem raz na godzinę?
Co prawda podczas używania to staję się przyjazne ale podczas edycji może się zrobić toporne, ciekawi mnie jak to wygląda w innych systemach które posiadają np takie a'la ścieżki. Trzeba podejreć, jeżeli ktoś ma ciekawy skrypt z dobrą metodą to niech wrzuci, przetestujemy go (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

Ten post edytował deirathe 25.01.2008, 13:32:20

[bim2]

[PHP] pobierz, plaintext 
<?php
public function selectCategories($iId=0, $sB4='', $sSelect = '')
	{	
		if(!$this->bHeader)
		{
			$this->bHeader = true;	
			$sSelect = '<option value="0">--SELECT--</option>';
 
		}
		$this->getAction('Categories', 'parseCategories');
		if($iId==0) 
		{
		$sClass = 'class="glowna" ';
		} else {
		$sClass = '';
		}
 
		if(!empty($this->aSelectCategories[$iId]))
		{
		foreach($this->aSelectCategories[$iId] AS $iKey => $sValue)
		{		
			$sSelect = $sSelect.'<option '.$sClass.'value="'.$sValue['id'].'"';
			if($sValue['id']==$this->catId)
			{
				$sSelect .=' selected="selected"';
			}
			$sSelect.='>'.$sB4.$sValue['name'].'</option>';
 
			$sSelect = $this->selectCategories($sValue['id'], $sB4.'- ', $sSelect);
		}
		}
		$this->sSelect = $sSelect.'<!---->';
		return	 $sSelect;
	}
?>
[PHP] pobierz, plaintext 

$B4 = before, czyli co ma byc przed optionem (IMG:http://forum.php.pl/style_emoticons/default/tongue.gif) np. ----

$this->getAction('Categories', 'parseCategories'); - pobiera kategorie o danym idku

@up
Po co utrudniac sobie zycie gdy można zrobić tak jak pokazałem+cache. Jak uaktualnisz wpis usuwasz cache i juz. (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)