Dotaz: MySQL procedúra a rekurzívne vyhľadávanie

[Peter]

Dobrý deň vám prajem. Mám 2 tabuľky:

t1

id | polozka
1  | darcekova krabica
2  | kancelarsky darcek
3  | pero
4  | ceruzka
5  | tuha
6  | voucher
7  | obal

t2

t1_id | obsah (opäť je to odkaz na t1.id)
1     | 2
1     | 6
2     | 3
2     | 4
4     | 5
4     | 7

Čiže máme tabuľku t1, ktorá obsahuje nejaký produkt, ktorý sa z niečoho skladá a to zloženie je definované v tabuľke t2. Čiže v tomto príklade za darčeková krabica skladá z kancelárskeho darčeka a vouchera. Ale kancelársky darček sa skladá z pera a ceruzky. A nakoniec ceruzka sa skladá z tuhy a obalu. A ja potrebujem všetky id z ktorých sa skladá nadradený produkt (v tomto príklade darčeková krabica). Predpokladám, že toto je úloha pre rekurziu a zatiaľ som stvoril takéto niečo:

delimiter //

create procedure get_product(in id int)

this_proc:begin
  declare loop_begin int;
  declare recursion int;

  set loop_begin=0;
  set recursion=0;

  set @trans=(select concat("trans_", replace(uuid(), '-', '')));

  set @create = concat('create temporary table if not exists ', @trans, '(id int unsigned not null auto_increment primary key, data int)');
  prepare stmt from @create;
  execute stmt;
  deallocate prepare stmt;

  set @insert = concat('insert into ', @trans, ' select NULL, obsah from t2 where t1_id=', id);
  prepare stmt from @insert;
  execute stmt;
  deallocate prepare stmt;

  set @select = concat('select max(id) into @loop_end from ', @trans);
  prepare stmt from @select;
  execute stmt;
  deallocate prepare stmt;


  if(@loop_end is not null) then
    while(loop_begin<@loop_end) do
      set loop_begin=loop_begin+1;
      set @select = concat('select data into @result from ', @trans, ' where id=', loop_begin);
      prepare stmt from @select;
      execute stmt;
      deallocate prepare stmt;
      set recursion=@result;
      select recursion;
      call get_product(recursion);
    end while;
  end if;
          
end//

delimiter ;

Ale táto procedúra mi končí keď sa dostane na položku ktorá už nikde neodkazuje. Čiže v tomto príklade dostanem 1,2,3.

Vie mi s tým niekto pomôcť? Za všetky rady vám vopred veľmi pekne ďakujem.

---

---

Odpovědi

[NN]

Domaci ukol?

[Peter]

Nie

[Georgeek]

Mrkni na tento odstavec: The Nested Set Model.

Je to sice jen princip, ale je to asi nejobecnější metoda pro každé SQL. Lépe to jde v PostgreSQL (SQLite, t-SQL), protože ten implementuje standard SQL99, který to umožňuje vyjádřit lépe a efektivněji.

[Josef Kufner]

Případně na totéž na Wikipedii.

Ve výsledku bude stačit jeden jednoduchý select na získání celého podstromu.

Pro snadnější renderování a opravy left/right indexů doporučuju držet parent ID jako primární údaj. Left/right pak dopočítáš (pomalým) rekurzivním algoritmem. Může se snadno stát, že updaty uděláš blbě a strom se rozsype (parent ID tě pak zachrání). Pro snadnost renderování nabídek doporučuju ještě dopočítávat hloubku ve stromu. Vystačíš si pak s jednoduchým nerekurzivním algoritmem, který ze selectu udělá stromové menu (hloubka se hodí hlavně na zavírání tagů). Tedy v databázi budeš mít uživatelem editované sloupečky id a parent_id. A pak tam budeš mít vypočítávané sloupečky tree_left, tree_right, tree_depth. Případně ještě můžeš přidat tree_root_id, pokud máš mnoho malých stromů (pro rychlejší updaty).