SELECT pola rodzica kategorii, dziedziczenie pola po rodzicu Opcje

[Athlan]

Witam,

posiadam tabelę 'categories':

-category_id int(11)
-category_parent int(11), default null
-category_price float(6,2), default null

Nie potrafię sobie poradzić z selectem, który wybierze kategorię o danym ID, a następnie sprawdzi cenę kategorii. Jeżeli cena nie została zdefiniowana (NULL), sprawdzi cenę rodzica category_parent, o ile został zdefiniowany. Jeżeli rodzic nie będzie miał ceny, sprawdzamy rodzica rodzica (dziadka). Aż do napotkania ceny lub rodzica, który nie ma rodzica, wówczas mamy NULL.

Drugie zapytanie jest analogiczne, ale chciałbym wyciągnąć nie tyle jeden rekord, co wszystkie, z ceną generowaną wg powyższej zasady.

Niestety, nie wiem w jaki sposób zapytać Google o takie rezultaty, zatem zapisałem posta. Jeżeli ktoś zechciałby się podzielić wiedzą - z góry wielkie dzięki, cisną mnie o to : )

Pozdrawiam serdecznie,
Athlan.

[phpion]

Bez rekurencji się tutaj raczej nie obejdzie. Najlepiej napisać to jako funkcję w MySQL i przenieść ciężar pobierania danych na bazę danych.

Jeśli jednak znasz/możesz założyć maksymalne zagłębienie kategorii to możesz zrobić to 1 zwykłym zapytaniem JOINując n razy kolejno tą samą tabelę czyli coś na zasadzie:

[SQL] pobierz, plaintext 
SELECT
	a.category_id AS category_id_1,
	a.category_price AS category_price_1,
 
	b.category_id AS category_id_2,
	b.category_price AS category_price_2,
 
	c.category_id AS category_id_3,
	c.category_price AS category_price_3
	-- itd...
FROM
	categories AS a
	LEFT JOIN categories AS b ON (b.category_id = a.category_parent)
	LEFT JOIN categories AS c ON (c.category_id = b.category_parent)
	-- itd...
[SQL] pobierz, plaintext 

Oczywiście spowoduje to dokonanie nadmiernej ilości złączeń, no ale mówi się trudno.

[Athlan]

Problem polega na tym, że nie zakładam zagłębień kategorii, więc to rozwiązanie jest nie do zrobienia.

Oczywiście, można zapisywać poziom zagłębienia i sztucznie generować joiny, ale dla drugiego zapytania to nie zadziała.

[phpion]

Na pewno nie możesz założyć jakiegoś maksymalnego zagłębienia? Jeśli znalazłbyś taką liczbę (powiedzmy 5 - bo po co więcej zagłębień) to wówczas masz załatwione oba zapytania. W przypadku gdy liczba ta okaże się zbyt mała zwiększysz ją (np. w jakimś konfigu) i po sprawie.

[Athlan]

Wowczas problem bylby trywialny. Potrzebuje wyciagnac dane zgodnie ze schematem : )

[phpion]

No to ostateczną cenę możesz wyciągnąć jako pierwsza wartość nie-NULL za pomocą COALESCE:

[SQL] pobierz, plaintext 
SELECT
	a.category_id AS category_id_1,
	a.category_price AS category_price_1,
 
	b.category_id AS category_id_2,
	b.category_price AS category_price_2,
 
	c.category_id AS category_id_3,
	c.category_price AS category_price_3,
	-- itd...
 
	COALESCE (category_price_1, category_price_2, category_price_3, ...) AS price
FROM
	categories AS a
	LEFT JOIN categories AS b ON (b.category_id = a.category_parent)
	LEFT JOIN categories AS c ON (c.category_id = b.category_parent)
	-- itd...
[SQL] pobierz, plaintext 

[Athlan]

@up: to już wiem, niektórzy programiści są tani i zaniżają rynek : ) Tanie rozwiązania są tańsze, uniwersalne są droższe.

Nie mogę użyć takiego rozwiązania. Muszę poradzić sobie rekurencją, natomiast nie wiem od jakiej strony to ugryźć.

[zegarek84]

jak już MySQL to w tej bazie masz coś takiego jak procedury - napisz sobie funkcję z wykorzystaniem while i przepisywaniem wartości do zmiennych:
http://maga.ovh.org/blog/?cat=8 - kliknij crlt+f i wklej "Jest również pętla WHILE"

MySQL Tutorial - Loop in Stored Procedures
są także REPEAT UNTIL oraz loop_label... a jeśli rozwiązać byś to chciał prosto w zwykłym sql, to o ile masz możliwość najprościej by było dopisać rekurencyjnie parametry drzewa nested tree z którego jednym zapytaniem możesz łańcuch rodziców...

a na wszelki wypadek jeszcze:
Variables in Stored Procedures

Ten post edytował zegarek84 9.11.2010, 18:52:05

[Athlan]

Zrobiłem. Prowizorka:

[SQL] pobierz, plaintext 
CREATE FUNCTION AnnouncementsCategoriesGetPrice(iCategoryId int(11)) RETURNS float(6,2)
BEGIN
  DECLARE iResult FLOAT(6,2);
  DECLARE iPointer INT(11);
 
  SET iPointer = iCategoryId;
  SET iResult  = NULL;
 
  WHILE (iPointer IS NOT NULL AND iPointer IS NOT NULL) DO
    SET iResult  = (SELECT category_data_price FROM cms_announcements_categories WHERE category_id = iPointer);
    SET iPointer = (SELECT category_parent FROM cms_announcements_categories WHERE category_id = iPointer);
  END WHILE;
 
  RETURN iResult;
END
[SQL] pobierz, plaintext