Base i stuktura drzewiasta.

Użytkowanie programu bazodanowego

Base i stuktura drzewiasta.

Postprzez szczur3k » Pn gru 09, 2019 10:10 am

W tabeli mam takie dane:

Kod: Zaznacz cały   Rozszerz widokZwiń widok
+-------+-------------------+-----------+
| Id    | Name              | Parent    |
+-------+-------------------+-----------+
| 1     | Kategoria 1       | 0         |
+-------+-------------------+-----------+
| 2     | Kategoria 2       | 0         |
+-------+-------------------+-----------+
| 3     | Kategoria 1-1     | 1         |
+-------+-------------------+-----------+
| 4     | Kategoria 1-2     | 1         |
+-------+-------------------+-----------+
| 5     | Kategoria 2-1     | 2         |
+-------+-------------------+-----------+
| 6     | Kategoria 1-2-1   | 4         |
+-------+-------------------+-----------+
| 7     | Kategoria 2-1     | 5         |
+-------+-------------------+-----------+
| 8     | Kategoria 2-2     | 2         |
+-------+-------------------+-----------+


I potrzebuję je w formularzu przedstawić jako:

Kategoria 1
Kategoria 1 -> Kategoria 1-1
Kategoria 1 -> Kategoria 1-1 -> Kategoria 1-2-1
Kategoria 1 -> Kategoria 1-2
Kategoria 2
Kategoria 2 -> Kategoria 2-1
Kategoria 2 -> Kategoria 2-1 -> Kategoria 2-1-1
Kategoria 2 -> Kategoria 2-2

Czyli takie klasyczne drzewko. Da się to w ogóle zrobić w BASE czy jednak to już lekka przesada?
Windows 10 (x64), Libre Office 6.2.7.1 (x86)
szczur3k
 
Posty: 9
Dołączył(a): Pt paź 11, 2019 12:14 pm

Re: Base i stuktura drzewiasta.

Postprzez Jan_J » Pn gru 09, 2019 1:38 pm

Jeżeli wysokość drzewa jest znana, można zbudować wielokrotny LEFT JOIN z kryterium postaci lewy.id = prawy.parent na każdym poziomie łączenia.
Struktury rekurencyjne w SQL znam zbyt słabo, by z pamięci zaproponować rozwiązanie ogólne.

Podpowiedź ze StackOverflow: https://stackoverflow.com/questions/175 ... -self-join, być może jest to dobry punkt wyjścia.

See also https://en.wikipedia.org/wiki/Hierarchi ... expression

Sam Base ma tu mniejsze znaczenie. Najwięcej zależy od wersji języka rozumianego przez system zarządzający bazą.
JJ
LO 6.2 ∙ AOO 4.1.7 ∙ Python 3.8 ∙ Unicode 13 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4157
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Base i stuktura drzewiasta.

Postprzez Rafkus » Śr kwi 22, 2020 1:16 pm

W dobie koronawirusa i wolnego czasu na wskutek izolacji (czyli nudy), zastanowiłem się nad tym tematem.
Stworzyłem takie oto zapytanko:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
SELECT "Id", "Name", "Parent"
   , casewhen( LOCATE( '-', "Name" ) = 0, "Name", LEFT( "Name", LOCATE( '-', "Name" ) - 1 ) ) AS "Kat1"
   , CASE WHEN LOCATE( '-', "Name" ) = 0 THEN null
         WHEN LOCATE( '-', "Name", LOCATE( '-', "Name" ) + 1 ) = 0 THEN "Name"
         ELSE LEFT( "Name", LOCATE( '-', "Name", LOCATE( '-', "Name" ) + 1 ) - 1 ) END AS "Kat2"
   , casewhen( LOCATE( '-', "Name", LOCATE( '-', "Name" ) + 1 ) = 0, null, "Name" ) AS "Kat3"
FROM "TabelaDanych" ORDER BY "Name" ASC
Ostatnio edytowano Cz kwi 23, 2020 6:14 am przez Rafkus, łącznie edytowano 1 raz
Powód: Zgodnie z sugesti zmieniłem w kodzie wartości puste ('') na null
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 163
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Base i stuktura drzewiasta.

Postprzez Jan_J » Śr kwi 22, 2020 8:58 pm

Ten pomysł polega na „rozpleceniu” rekurencji za pomocą wyrażeń wariantowych CASEWHEN do głębokości 3 włącznie.
Jeśli mamy ograniczenie głębokości, to ja bym proponował w zamian jednak LEFT JOIN trzykrotny. Piszę kod na pałę bez weryfikacji, więc może zawierać błąd formalny.
Kod: Zaznacz cały   Rozszerz widokZwiń widok
SELECT T1.Name as Kat1, T2.Name as Kat12, T3.Name as Kat123
FROM TabelaDanych as T1
LEFT JOIN TabelaDanych as T2 ON (T1.Id = T2.Parent)
LEFT JOIN TabelaDanych as T3 ON (T2.Id = T3.Parent)
ORDER BY Kat1, Kat12, Kat123;

(Zamiast '' (pusty tekst) mamy tu brak wpisu (NULL), co wydaje się nawet właściwsze. Ale w propozycji @Rafkus-a także da się to osiągnąć bez trudności.)

Współczesne serwery baz danych są w stanie przeprowadzić operację złączenia rekurencyjnego na podstawie dyrektywy języka. Nigdy nie zgłębiałem tematu ani pod względem zgodności ze standardami SQL, ani stanu realizacji w RDBMS-ach.
Być może warto. Cytując (za Wikipedią https://en.wikipedia.org/wiki/Hierarchi ... expression, z odwołaniami do dokumentacji niektórych wymienionych systemów):
Recursive CTEs are also supported by Microsoft SQL Server (since SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, IBM Informix version 11.50+, CUBRID, MariaDB 10.2+ and MySQL 8.0.1+.

Co nie oznacza, że konstrukcja zostanie zaakceptowana w kwerendzie przez intefejs Base'a. Ale jako perspektywa istniejąca w systemie – już tak, bo w tym zakresie Base nie ma nic do gadania.

Zwrócę jeszcze uwagę, że jeśli porządek wyznaczony przez pary Id/Parent nie będzie drzewiasty (tj. będzie zawierał cykle), to rekurencja się nie skończy. Wymuszenie porządku acyklicznego w bazie za pomocą kombinacji prostych dyrektyw FOREIGN KEY i CHECK nie uda się. Ale można zaprojektować wyzwalacz.
JJ
LO 6.2 ∙ AOO 4.1.7 ∙ Python 3.8 ∙ Unicode 13 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4157
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Base i stuktura drzewiasta.

Postprzez Rafkus » Cz kwi 23, 2020 11:09 am

No cóż, na podstawie odpowiedzi @Jan_J stwierdzam, że mogłem zbyt dosłownie podejść do problemu.
Zrozumiałem o co chodzi z kolumną Parent (rodzic). Otóż mogą być wprowadzane dane:
Id = 1; Name = nazwa owocu; Parent = 0; (nowy element, dlatego Parent = 0)
Id = 2; Name = nowy owoc; Parent = 0; (nowy element, dlatego Parent = 0)
Id = 3; Name = odmiana owocu; Parent = I; (pozycja ta dotyczy rekordu wprowadzonego wcześniej do Id=1, Parent = Id )
Id = 4; Name = kolor owocu; Parent = 3; (pozycja ta dotyczy rekordu wprowadzonego wcześniej do Id=3, Parent= Id )
itd...
Dla takich rekordów moja wcześniejsza odpowiedź jest nie przydatna. :(
Dla nich zbudowałbym formularz z pod formularzami:
- formularzem głównym byłaby przefiltrowana TabelaDanych, warunek filtru to Parent = 0;
- podfolmularzem byłaby ta sama TabelaDanych, połączenie danych odbywałoby się dzięki polu formularza glównego Id a w podformularzu Parent ;
- pod-podfolmularzem byłaby znowu TabelaDanych, połączenie danych odbyło by się analogicznie tak jak wcześniej czyli dzięki polom Id (podformularza) i Parent (pod-podformularza)

-----------------------------------------
Edit 1:
Bawiłem się kodem zaproponowanym przez @Jan_J i dzięki niemu udało się uzyskać wymagany efekt:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
SELECT "Id", "Name", "Parent",
    CASE WHEN "Parent" = 0 THEN "Name" ELSE "Kat1" END AS "kol1",
    CASE WHEN "Parent" = 0 THEN NULL ELSE "Kat12" END AS "kol2",
    CASE WHEN "Id" = "ID3" THEN "Kat123" ELSE NULL END AS "kol3"
FROM "TabelaDanych" LEFT JOIN (
   SELECT "T1"."Name" AS "Kat1", "T2"."Name" AS "Kat12", "T3"."Name" AS "Kat123",
       "T2"."Id" AS "ID2", "T3"."Id" AS "ID3"
   FROM "TabelaDanych" AS "T1"
      LEFT JOIN "TabelaDanych" AS "T2" ON ( "T1"."Id" = "T2"."Parent" )
      LEFT JOIN "TabelaDanych" AS "T3" ON ( "T2"."Id" = "T3"."Parent" )
   WHERE "T1"."Parent" = 0
                  ) AS "T" ON ( "TabelaDanych"."Id" = "T"."ID2" OR "TabelaDanych"."Id" = "T"."ID3" )
ORDER BY "kol1" ASC, "kol2" ASC, "kol3" ASC


-----------------------------------------
Edit 2:
Eksperymentowałem z HSQL w wersji 2.3. - on obsługuje "Struktury rekurencyjne", wymagany efekt uzyskałem tworząc kwerendę SQL:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
WITH RECURSIVE "tree" ("Pokolenie", "Rodzic", "OsobaId","Kat1","Kat12","Kat123") AS
   (
   SELECT 1, "Parlent", "Id","Name",null,null FROM "TabelaDanych" WHERE "Parlent" = 0
   UNION
   SELECT "Pokolenie" + 1, "Parlent", "Id","Kat1",
      case when "Pokolenie" + 1 = 2 then "Name" else "Kat12"end,
      case when "Pokolenie" + 1 = 3 then "Name" else null end
   FROM "TabelaDanych", "tree" WHERE "Parlent" = "OsobaId"
   ) SELECT * FROM "tree" order by "Kat1","Kat12"

Polecenie to działa tylko z silnikiem HSQLDB 2.3 oraz przy wciśniętym przycisku "WYKONAJ BEZPOŚREDNIO POLECENIE SQL..."
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 163
Dołączył(a): Cz kwi 12, 2018 10:26 pm


Powrót do Base

Kto przegląda forum

Użytkownicy przeglądający ten dział: Brak zidentyfikowanych użytkowników i 1 gość