mysql hierarchy self-join, recupera tutte le sottocategorie

Ho delle categories tabelle che contengono le seguenti colonne:

 category_id category_name parent_id 

Ho bisogno di ottenere l’elenco di tutte le sottocategorie di tutti i livelli per una determinata categoria principale, quindi se ad esempio fornisco l’id di qualche categoria di lvl 3 vorrei tornare all’elenco di tutte le classi lvl 4, 5, 6 … che sono figli di quella categoria lvl 3.

Nessuna gerarchia deve essere preservata, solo una semplice lista.

Prima pensavo di farlo solo con diversi join e sottoquery, ma poi ho pensato che le categorie sarebbero state molto più profonde in seguito, quindi non è un modo per andare.

Da quando ho appena avviato SQL, non so ancora come scrivere query ricorsive, quindi questo sarebbe un grande aiuto e materiale didattico.

Leggi per Please Note in fondo prima. Ok bene, sei tornato.

Creazione di una stored procedure per il recupero della gerarchia ricorsiva.

Nota, non lo volevi per livelli, ma ciò può essere fatto facilmente.

Schema:

 create table category ( category_id int not null auto_increment primary key, category_name varchar(40) not null, parent_id int null, -- index on this column not a shabby idea unique key (category_name) ); insert category(category_name,parent_id) values ('car',null),('food',null); -- 1,2 insert category(category_name,parent_id) values ('ford',1),('chevy',1),('fruit',2); -- 3,4,5 insert category(category_name,parent_id) values ('economy',3),('escort',6),('exhaust',7); -- 6,7,8 insert category(category_name,parent_id) values ('chassis',7),('loud',8),('banana',5); -- 9,10,11 -- ok granted I could have explicity inserted category_id to make it more obvious 

Crea processo memorizzato:

 -- drop procedure showHierarchyBelow; delimiter $$ create procedure showHierarchyBelow ( catname varchar(40) ) BEGIN -- deleteMe parameter means i am anywhere in hierarchy of role -- and i want me and all my offspring deleted (no orphaning of children or theirs) declare bDoneYet boolean default false; declare working_on int; declare theCount int; declare findFirst int; select ifnull(category_id,0) into findFirst from category where category_name=catname; CREATE TABLE xx_RecursishHelper_xx ( -- it's recurshish, not recursive category_id int not null, processed int not null ); if isnull(findFirst) then set findFirst=0; end if; insert into xx_RecursishHelper_xx (category_id,processed) select findFirst,0; if (findFirst=0) then set bDoneYet=true; else set bDoneYet=false; end if; while (!bDoneYet) do -- I am not proud of this next line, but oh well select count(*) into theCount from xx_RecursishHelper_xx where processed=0; if (theCount=0) then -- found em all set bDoneYet=true; else -- one not processed yet, insert its children for processing SELECT category_id INTO working_on FROM xx_RecursishHelper_xx where processed=0 limit 1; insert into xx_RecursishHelper_xx (category_id,processed) select category_id,0 from category where parent_id=working_on; -- mark the one we "processed for children" as processed update xx_RecursishHelper_xx set processed=1 where category_id=working_on; end if; end while; delete from xx_RecursishHelper_xx where category_id=findFirst; select x.category_id,c.category_name from xx_RecursishHelper_xx x join category c on c.category_id=x.category_id; drop table xx_RecursishHelper_xx; END $$ 

Proc. Memorizzata test:

 call showHierarchyBelow('food'); +-------------+---------------+ | category_id | category_name | +-------------+---------------+ | 5 | fruit | | 11 | banana | +-------------+---------------+ call showHierarchyBelow('car'); +-------------+---------------+ | category_id | category_name | +-------------+---------------+ | 3 | ford | | 4 | chevy | | 6 | economy | | 7 | escort | | 8 | exhaust | | 9 | chassis | | 10 | loud | +-------------+---------------+ call showHierarchyBelow('ford'); +-------------+---------------+ | category_id | category_name | +-------------+---------------+ | 6 | economy | | 7 | escort | | 8 | exhaust | | 9 | chassis | | 10 | loud | +-------------+---------------+ call showHierarchyBelow('xxx'); -- no rows 

Nota Ho semplicemente modificato questa mia risposta di alcuni mesi fa per le tue esigenze.

Notare che

Quanto sopra è solo a scopo illustrativo. In una situazione del mondo reale, non creerei mai tabelle in un proc memorizzato. Il sovraccarico del DDL è significativo. Invece, userei le tabelle non temporanee preesistenti con un concetto di sessione. E pulisci le righe per la sessione. Quindi non prendere il sopra come più di un uomo di paglia, in attesa di renderlo più performante in quanto tale. Chiedi se questo è confuso.