tabelle e ricorsività in mysql

[Enrico Modanese]

Ciao, ti scrivo perchè uso da tempo strutture ad albero con MySQL. Alcune risposte:

A - come fare se alcuni livelli dell'albero (magari l'ultimo) hanno dei dati in più da salvare? No problem: basta creare una tabella di estensione con chiave+valori. Il problema è logico: una gerarchia ad albero a infiniti livelli dovrebbe contenere oggetti tutti dello stesso tipo. Se i tipi sono diversi non possono essere infiniti, allora non ti serve un albero, ma una cascata di tabelle in gerarchia. Cioè: articolo figlio di articolo figlio di articolo .... è logicamente diverso articolo figlio di categoria figlio di macrocategoria. Nel primo caso usi una tabella ricorsiva a chiave figlio/padre, nel secondo tre tabelle (potranno essere 4, 5 ma non infinite e comunque in numero certo).

B - Non esiste SQL puro ricorsivo. Cioè non puoi avere un albero nel risultato di una query. Il motivo è semplice: le colonne devono essere in numero predefinito. Quindi puoi vedere 2,3 livelli ma non n. Esistono solo due modi per fare ricorsione: o una stored procedure (scripting per sql) oppure da codice (ad esempio php). Nel secondo caso semplicemente cerchi i figli con padre uguale ai figli del giro precedente etc... Per rappresentare in html: usi nella funzione ricorsiva una variabile globale che conta i giri ed aggiunge una indentazione ad ogni nuova riga. Esistono diversi modi per rendere alberelli in html.

C - Prestazioni. Anche se hai migliaia di righe è veloce, purchè tu abbia buone chiavi (usa degli interi) e il codice ricorsivo sia breve. Il motivo è semplice: il numero di iterazioni-query è pari ai nodi dell'albero, che sono in numero finito. Di solito la parte più lenta non è MySQL ma la rappresentazione html.

[13manuel84]

Originariamente inviato da Enrico Modanese
Ciao, ti scrivo perchè uso da tempo strutture ad albero con MySQL. Alcune risposte:

Ti ringrazio per tutti i chiarimenti che mi hai dato!

A - come fare se alcuni livelli dell'albero (magari l'ultimo) hanno dei dati in più da salvare? No problem: basta creare una tabella di estensione con chiave+valori. Il problema è logico: una gerarchia ad albero a infiniti livelli dovrebbe contenere oggetti tutti dello stesso tipo. Se i tipi sono diversi non possono essere infiniti, allora non ti serve un albero, ma una cascata di tabelle in gerarchia. Cioè: articolo figlio di articolo figlio di articolo .... è logicamente diverso articolo figlio di categoria figlio di macrocategoria. Nel primo caso usi una tabella ricorsiva a chiave figlio/padre, nel secondo tre tabelle (potranno essere 4, 5 ma non infinite e comunque in numero certo).

La mia struttura necessita di un nodo finale, la foglia, che sia totalmente diverso dai livelli precedenti. in quanto deve avere dei campi in più. Inoltre la mia struttura deve prevedere che ad esempio un nodo abbia tre livelli e poi la foglia, mentre un'altro ramo può avere quattro livelli e poi la foglia, oppure anche solo due livelli compresa la foglia etc.. Capisci quindi che essa deve essere completamente dinamica ed ogni ramo deve essere completamente indipendente dall'altro.
Non avevo preso in considerazione la soluzione che hai postato tu, di avere una tabella che che contenga una "combinazione" di chiavi+valori, l'avevo posta molto più semplicemente, anche se magari non sarà la soluzione migliore, avendo una sola tabella diversa che mi rappresenti le foglie. Ma mi crea già diversi problemi solo a livello progettuale.

B - Non esiste SQL puro ricorsivo. Cioè non puoi avere un albero nel risultato di una query. Il motivo è semplice: le colonne devono essere in numero predefinito. Quindi puoi vedere 2,3 livelli ma non n. Esistono solo due modi per fare ricorsione: o una stored procedure (scripting per sql) oppure da codice (ad esempio php). Nel secondo caso semplicemente cerchi i figli con padre uguale ai figli del giro precedente etc... Per rappresentare in html: usi nella funzione ricorsiva una variabile globale che conta i giri ed aggiunge una indentazione ad ogni nuova riga. Esistono diversi modi per rendere alberelli in html.

Che non esiste SQL puro ricorsivo lo ipotizzavo e ne ho avuto la conferma leggendo vari articoli, tra cui quello consigliato in questo thread. La ricorsività che intendevo io era data da una funzione che eseguisse in loop una determinata query, passando di volta in volta diversi parametri [che può essere il parent_id], e ricostruisse la struttura gerarchica. Come effettivamente fa la funzione che ha postato andr3a. L'unico problema è, come ho già spiegato, che non riesco a rendere dinamica la struttura. Io non la devo stampare tutta come illustrata nell'esempio, ma devo implementare una struttura del tutto similare alla gestione delle categorie operata da google, yahoo, dmoz e diversi altri.

C - Prestazioni. Anche se hai migliaia di righe è veloce, purchè tu abbia buone chiavi (usa degli interi) e il codice ricorsivo sia breve. Il motivo è semplice: il numero di iterazioni-query è pari ai nodi dell'albero, che sono in numero finito. Di solito la parte più lenta non è MySQL ma la rappresentazione html.

Per come sto progettando io il numero di query dovrebbe essere una per ogni pagina, ma il problema è che non sapendo quanti livelli ho non posso stabilire quante pagine sono, e quindi sono un pò bloccato su questo. Una soluzione che vedo io è quella di mettere tutto in una pagina, ma non vorrei che così facendo mi si incasinasse troppo il codice e che la pagina assumesse dimensioni spropositate, diventando di conseguenza praticamente impossibile da caricare.

[andr3a]

ci sono quasi

questo e' l' ultimo risultato ottenuto:

array(2) {
  ["ARTICOLI"]=>
  array(3) {
    ["_description_"]=>
    string(22) "Utensili di vario tipo"
    ["MARTELLI"]=>
    array(3) {
      ["_description_"]=>
      string(20) "Questi sono martelli"
      ["DI LEGNO"]=>
      array(3) {
        ["_description_"]=>
        string(17) "Martelli di legno"
        [0]=>
        array(3) {
          [0]=>
          string(1) "2"
          [1]=>
          string(8) "DI LEGNO"
          [2]=>
          string(24) "martello di legno tipo C"
        }
        [1]=>
        array(3) {
          [0]=>
          string(1) "5"
          [1]=>
          string(8) "DI LEGNO"
          [2]=>
          string(24) "Martelli di legno tipo A"
        }
      }
      ["DI FERRO"]=>
      array(2) {
        ["_description_"]=>
        string(17) "Martelli di ferro"
        [0]=>
        array(3) {
          [0]=>
          string(1) "3"
          [1]=>
          string(8) "DI FERRO"
          [2]=>
          string(24) "martello di ferro tipo Z"
        }
      }
    }
    ["FORBICI"]=>
    array(2) {
      ["_description_"]=>
      string(19) "Queste sono forbici"
      [0]=>
      array(3) {
        [0]=>
        string(1) "4"
        [1]=>
        string(7) "FORBICI"
        [2]=>
        string(17) "forbici per carta"
      }
    }
  }
  ["GIOCHI"]=>
  array(2) {
    ["_description_"]=>
    string(16) "Giochi per il PC"
    [0]=>
    array(3) {
      [0]=>
      string(1) "1"
      [1]=>
      string(6) "GIOCHI"
      [2]=>
      string(13) "QUAKE 3 ARENA"
    }
  }
}

a me pare ok


c'ho bestemmiato un po' sopra ma alla fine con una classe ( ) sono riuscito a fare quello che volevo, gerarchia di classi e sottoclassi teoricamente infinite, metodo che ti avevo detto con un' unica enorme differenza, la tabella che contiene gli articoli te la fai come cavolo ti pare ed e' completamente indipendente dalla gerarchia e dal sistema stesso ... quindi non hai alcun vincolo, se non quello di doverti ritrovare i risultati , dentro l'array generato, come ROWS e non come NOME CAMPO ... beh, a me non sembra poi questa tragedia, poi magari un domani modifichero' per far si che tu possa per ogni articolo utile estrapolare i dati come NOME_CAMPO e non come rows ... ma chissenefrega per ora


IMPORTANTISSIMO:
l' unica cosa che terra' legata la tua tabella FOGLIE al sistema e' un campo, di nome parent_label di tipo VARCHAR(255) , o anche meno di 255 ... stop

non c'e' altro che devi fare, la classe e il sistema prende in automatico tutte le informazioni utili e valide, scartando i rami "senza foglie"


appena la finisco te la posto , per ora stai tuned

[andr3a]

ti faccio l' esempio che ho usato io

$db = mysql_connect( 'localhost', 'root', '' );

$dbtree = &new MySQL_LevelManager( $db, 'prove' ); // passi la connessione al db mysql e il nome del database

$dbtree->setLevels(3); // setti i livelli, da usare solo in amministrazione ... ne vuoi aggiungere 1 ? $dbtree->setLevels(4); ...

$dbtree->addSubLevel( NULL, 'ARTICOLI', 'Utensili di vario tipo' ); // aggiungi una radice, con NULL per prima voce
$dbtree->addSubLevel( NULL, 'GIOCHI', 'Giochi per il PC' );

$dbtree->addSubLevel( 'ARTICOLI', 'MARTELLI', 'Questi sono martelli' ); // aggiungi ramo MARTELLI alla RADICE ARTICOLI ( e descrizione )
$dbtree->addSubLevel( 'ARTICOLI', 'FORBICI', 'Queste sono forbici' ); // aggiungi ramo FORBICI alla RADICE ARTICOLI ( e descrizione )
$dbtree->addSubLevel( 'MARTELLI', 'DI LEGNO', 'Martelli di legno' ); // aggiungi ramo DI LEGNO alla RADICE MARTELLI ( e descrizione )
$dbtree->addSubLevel( 'MARTELLI', 'DI FERRO', 'Martelli di ferro' ); // aggiungi ramo DI FERRO alla RADICE MARTELLI ( e descrizione )

// popolamento TABELLA FOGLIE ... fata come ti pare ... MA
// per risalire alla gerarchia devi mettere la label o nome della radice padre
// ESEMPIO:
INSERT INTO MIE_FOGLIE VALUES( '', 'DI LEGNO', 'martello legno tipo 4', 'peso 7kg', '' ... etc etc  )
// "DI LEGNO" lo schiaffera' sotto ramo DI LEGNO in gerarchia


// ESEMPIO RISULTATO OTTENUTO
$queryTree = &$dbtree->getArray( 'MIE_FOGLIE' );
if( $queryTree !== false ) {
    echo '<pre>';
    var_dump( $queryTree );
    echo '</pre>';
} 

alla classe aggiungero' delSubLevel con force per i sottoArticoli ... poi penso basta, la debuggo un po' e la posto

[13manuel84]

Mamma!!ti ringrazio infinitamente!spero di poter adattare il tutto alle mie esigenze!

Hai dato un'occhiata al mio post precedente?con una struttura del genere come posso implementare la navigazione tra i vari livelli?devo fare tutto in un'unica pagina?quello che intendo, è che la navigabilità deve essere del tutto simile a questa [passando magari in querystring i valori o i nomi delle tabelle o i vari parent_id].

È possibile o sto farneticando?

Ti ringrazio ancora moltissimo!!

[andr3a]

bene, mi sono appena reso conto che pero' cosi' e' limitatissimo ... cioe', mettiamo caso

MONITORS -> TOSHIBA, THOMSON
PORTATILI -> TOSHIBA, THOMSON

... e tutta sta cosa se ne va aff ...








... ci pensero' su

[andr3a]

mappati con successo .. ora devo gestire l' amministrazione del metodo ... per percorsi ... ogni suggerimento e' ben accetto ... query ed elaborazione dinamica per ora riuscita con successo ...


SELECT * FROM __category__0 LEFT JOIN __category__1 ON __category__0.id = __category__1.parent_id LEFT JOIN __category__2 ON __category__1.id = __category__2.parent_id LEFT JOIN my_last_table ON my_last_table.parent_id = CONCAT_WS( "|", __category__0.id, __category__1.id, __category__2.id )

array(2) {
  ["ARTICOLI"]=>
  array(3) {
    ["_description_"]=>
    string(22) "Utensili di vario tipo"
    ["MARTELLI"]=>
    array(3) {
      ["_description_"]=>
      string(20) "Questi sono martelli"
      ["DI LEGNO"]=>
      array(3) {
        ["_description_"]=>
        string(17) "Martelli di legno"
        [0]=>
        array(3) {
          [0]=>
          string(1) "2"
          [1]=>
          string(5) "1|1|1"
          [2]=>
          string(24) "martello di legno tipo C"
        }
        [1]=>
        array(3) {
          [0]=>
          string(1) "5"
          [1]=>
          string(5) "1|1|1"
          [2]=>
          string(24) "Martelli di legno tipo A"
        }
      }
      ["DI FERRO"]=>
      array(2) {
        ["_description_"]=>
        string(17) "Martelli di ferro"
        [0]=>
        array(3) {
          [0]=>
          string(1) "3"
          [1]=>
          string(5) "1|1|2"
          [2]=>
          string(24) "martello di ferro tipo Z"
        }
      }
    }
    ["FORBICI"]=>
    array(2) {
      ["_description_"]=>
      string(19) "Queste sono forbici"
      [0]=>
      array(3) {
        [0]=>
        string(1) "4"
        [1]=>
        string(3) "1|2"
        [2]=>
        string(17) "forbici per carta"
      }
    }
  }
  ["GIOCHI"]=>
  array(2) {
    ["_description_"]=>
    string(16) "Giochi per il PC"
    [0]=>
    array(3) {
      [0]=>
      string(1) "1"
      [1]=>
      string(1) "2"
      [2]=>
      string(13) "QUAKE 3 ARENA"
    }
  }
}


0.0027871131896973

[13manuel84]

Ti chiedo scusa, ma mi sto un attimo perdendo. Il codice php che hai postato 2messaggi sopra serve ancora o no?poi ci sarebbe che non riesco a capire cosa fa quel codice, fa utilizzo di alcune classi che non conosco e non so cosa elaborano.

Mentre il codice che hai postato qui sopra?la query la devo sostituire alla mia?come viene generata?perché tutti quegli underscore?li mette il forum?la query è valida per la struttura del database che abbiamo visto all'inizio?

Scusami se ti faccio così tante domande e se ci capisco poco, ma se non capisco all'inizio i vari passaggi sarà dura che riesca ad implementare una funzione del genere.

Grazie mille!

[13manuel84]

Ho un dubbio che nessuno mi ha ancora chiarito, e prima di andare avant vorrei aver ben chiaro in testa come procedere.

Dunque, io nella mia struttura ho un albero gerarchico con diversi rami, ogni ramo è un discorso a sè, e ce n'è uno che può avere 3livelli, un altro può averne anche solo 1, o altri ancora possono averne 4 etc.

Ora, quello che si era pensato all'inizio era di far visualizzare ogni livello in una pagina a sè, quindi se ho 4livelli avrò 4pagine. Ma appunto il mio problema è che NON SO quanti livelli avrò, quindi come faccio a preparare [o meglio dire ai grafici di preparere ] un layout per questo discorso?

Devo gestirmi tutto in un'unica pagina?e se si, la pagina non diverrà troppo pesante da caricare?o altrimenti, cè un metodo comunque per gestire tutto in una pagina ma ridurre al minimo la memoria occupate?o ancora, non esiste un modo per dirgli comunque di caricare il tutto in pagine diverse?
Ad esempio, se ho un massimo di 5livelli, faccio preparare 5pagine diverse, ma come faccio a sapere quando mi devo fermare alla terza e quando invece devo proseguire fino alla quinta??

Spero di essermi spiegato, e che qualcuno riesca a chiarirmi questo dubbio!

[andr3a]

Originariamente inviato da 13manuel84
Ti chiedo scusa, ma mi sto un attimo perdendo. Il codice php che hai postato 2messaggi sopra serve ancora o no?poi ci sarebbe che non riesco a capire cosa fa quel codice, fa utilizzo di alcune classi che non conosco e non so cosa elaborano.

alcune classi ??? ... la classe e' una sola e la sto scrivendo io, sfido che la conosci

Originariamente inviato da 13manuel84
Mentre il codice che hai postato qui sopra?la query la devo sostituire alla mia?come viene generata?perché tutti quegli underscore?li mette il forum?la query è valida per la struttura del database che abbiamo visto all'inizio?

il codice e' il risultato , in un array generato automaticamente dalla classe, del tuo albero , qualunque esso sia e come puoi notare se ne frega che un ramo abbia 4 livelli oppure 1, l' array rispecchia la gerarchia dell' albero ed e' strutturato come avevamo detto, ovvero rami indipendenti per foglie indipendenti.
Gli underscores sono per le tabelle "fantasma" che crea la classe, sono i rami, o livelli, che possono andare da 1 a 1000 o anche piu', sta alla potenza del PC dopo supportare una grande mole di dati ... tu non ti devi preoccupare di niente, ogni livello o ramo avra' un nome ed una descrizione, non credo serva altro per il ramo .... quello che serve poi e' la singola foglia, ma per ogni fine del ramo avrai tutti i dati in tabella per ogni foglia trovata di quel ramo, unico inconveniente , come ti avevo detto, e' che i dati saranno in rows e non in associativi ( ma ti basta mappare un solo array o fare una funzione a parte che ri prendi i dati che ti servono da questo array albero ... ).
La gestione di quella tabella e' semi automatizzata, semi perche' in area amministrazione dovrai fare dei constrolli, se in db non c'e' niente e di punto in bianco vuoi il ramo 'PIPPO' sotto 'PLUTO' e 'PAPERINO' devi settare tu il livello a 3, poi crei , con il nuovo metodo:

$dbtree->addSubLevel( NULL, 'PIPPO', 'Ramo Pippo' );
$dbtree->addSubLevel( 'PIPPO', 'PLUTO', 'Ramo Pluto di Pippo' );
$dbtree->addSubLevel( Array( 'PIPPO', 'PLUTO' ), 'PAPERINO', 'Ramo Paperino di Pluto che e' ramo di Pippo' );

Quando vorrai indicizzare una foglia nel campo parent_id, che dovra' essere di varchar(255) ( o anche TEXT ma varchar ti garantisce fino a 50 livelli o forse piu' ... ), unico vincolo per la tua tabella foglie, avrai a disposizione un metodo della classe che mappera' in automatico il percorso, sempre che lo trova ( per ogni operazione la classe restituisce false se non e' stata in grado di fare cio' che hai richiesto ... )

Esempio:

'INSERT INTO foglie VALUES ( "", "'.$dbtree->getNode( 'PIPPO', 'PLUTO', 'PAPERINO' ).'", "Descrizione Foglia", "altro valore", "altro ancora", "quello che ti pare", ... etc etc ... )'

Originariamente inviato da 13manuel84
Scusami se ti faccio così tante domande e se ci capisco poco, ma se non capisco all'inizio i vari passaggi sarà dura che riesca ad implementare una funzione del genere.

Grazie mille!

Fondamentalmente la parte che devi curare MOLTO BENE e' quella di gestione / amministrazione delle foglie, dei rami e dei sotto - menu' ... la quale sara' relativamente complessa o lenta ( mai sopra 0.1 di risultato per tutte le operazioni che ho tentato ) mentre per gli utenti che navigheranno il sito userai un solo metodo della classe:

$dbtree->getArray( 'foglie' );
il quale restituira' false se qualcosa non e' andato bene, ma questo e' quasi impossibile se gestisci bene l' area di amministrazione, altrimenti restituira' l' array che ti ho mostrato sopra ... quello mappato con tutti i rami, categorie e informazioni sulle foglie di ogni ramo.

Quando hai un array cosi', hai tutta la liberta' di gestirtelo come ti pare, ricorda che oltre ad essere mappato ha degli indici che se impostati in get o post ti possono far vedere l' albero come caolo ti pare ...


Questo non so se e' quello che cercavi, a me questa cosa sta piacendo parecchio e se non ti serve mi spiace ma per quel che mi riguarda e' la soluzione definitiva a tutti i miei problemi di gestione menu' , sottomenu', gerarchie o altro .. almeno finche' non mi accorgo che qualcosa non puo' andare o non rispetta cio' che deve rispettare, la gerarchia.


Dimenticavo:
totale query per visualizzazione albero nell' array: 2
di cui 1 rapidissima per la sola lettura dei livelli, e l' altra unica per avere tutta la gerarchia

I tempi li sto ottimizzando, l'ultimo array visualizzato l' ha creato in 0.004491 ... ed ha un elemento in piu' rispetto quelli mostrati prima