31/05/11

Hiërarchische data verwijderen in het Adjacency List Model (MySQL)

Het Adjacency List Model is een database model waarmee je in MySQL hiërarchische data kunt beheren. Het Adjacency List Model werkt eigenlijk heel eenvoudig. In de tabel met items (categorieën en producten) voeg je een ‘parent_id’ kolom toe. In deze kolom komt bij elk item de directe parent_id van het item te staan.

Voorbeeld van een productlijst van een dierenwinkel:

Dieren
     Honden
          Hondeneten
               Met vis
               Met kip
          Manden
               Stoffen mand
               Plastic mand
     Katten
          Katteneten
               Met vis
               Met kip
          Manden
               Stoffen mand
               Plastic mand

Bovenstaand voorbeeld ziet er volgens het Adjacency List Model in een MySQL database zo uit:

ID description parent_id type
1 Dieren 1
2 Honden 1 1
3 Hondeneten 2 1
4 Met vis 3 0
5 Met kip 3 0
6 Manden 2 1
7 Stoffen mand 6 0
8 Plastic mand 6 0
9 Katten 1 1
10 Katteneten 3 1
11 Met vis 10 0
12 Met kip 10 0
13 Manden 9 1
14 Stoffen mand 13 0
15 Plastic mand 13 0

* type 0 = product, type 1 = categorie

Categorieën en producten selecteren

Als je bv. alle categorieën en producten in een bepaalde categorie wilt ophalen kan je de volgende eenvoudige query gebruiken:

Query

"SELECT * FROM 'items' WHERE 'parent_id' = '2'"

Resultaat

     Hondeneten
     Manden

Categorie (of product) toevoegen

Als je bv. in de categorie ‘katteneten’ het product ‘Met rund’ wil toevoegen, kan je de volgende query gebruiken:

Query

"INSERT INTO 'items' ('description', 'parent_id', 'type') VALUES ('Met rund', '10', '0')"

Resultaat

ID description parent_id type
1 Dieren 1
2 Honden 1 1
3 Hondeneten 2 1
4 Met vis 3 0
5 Met kip 3 0
6 Manden 2 1
7 Stoffen mand 6 0
8 Plastic mand 6 0
9 Katten 1 1
10 Katteneten 3 1
11 Met vis 10 0
12 Met kip 10 0
13 Manden 9 1
14 Stoffen mand 13 0
15 Plastic mand 13 0
16 Met rund 10 0

Categorie (of product) verplaatsen

Dit database model maakt het ook heel makkelijk om categorieën te verplaatsen naar andere categorieën:

Query

"UPDATE 'items' SET 'parent_id' = '3' WHERE 'id' = '16'"

Resultaat

ID description parent_id type
1 Dieren 1
2 Honden 1 1
3 Hondeneten 2 1
4 Met vis 3 0
5 Met kip 3 0
6 Manden 2 1
7 Stoffen mand 6 0
8 Plastic mand 6 0
9 Katten 1 1
10 Katteneten 3 1
11 Met vis 10 0
12 Met kip 10 0
13 Manden 9 1
14 Stoffen mand 13 0
15 Plastic mand 13 0
16 Met rund 3 0

Categorie verwijderen

Nu wordt het moeilijk. Wanneer je een categorie verwijderd met één query kan je ten hoogste de categorie zelf samen met alle categorieën en producten, die deze categorie als directe parent hebben, verwijderen. Dit is nu net wat we willen, behalve dan dat we niet weten of er nog meer categorieën en producten in onze verwijderde items zitten, en in deze categorieën misschien wel nog meer categorieën, enzoverder … je begrijpt het probleem. Het Adjacency List Model maakt het niet mogelijk om recursief categorieën en producten op te halen uit onze database, zeker niet als je met onbekende diepte zit. Welja, om precies te zijn maakt MySQL dit niet mogelijk in dit database model. MySQL ondersteund immers geen recursieve syntax. PostgreSQL, Oracle 11g en MSSQL ondersteunen dit wel.

Ik vind echter de andere functies (selecteren, invoegen, updaten) zo eenvoudig bij dit database model, zeker in vergelijking met het ‘Nested Set Model‘, dat ik dit model om hierarchische data op te slagen in MySQL niet zo maar wou laten varen. Daarom heb ik mijn aandacht verlegd van MySQL naar PHP door middel van met een recursieve functie te werken. Een recursieve functie is een functie die zichzelf opnieuw aanroept. Eenvoudig zouden we kunnen stellen dat als we een categorie verwijderen, we de ‘childnodes’ van deze categorie uit de database halen, dan met een foreach loop in PHP het type van elk item nakijken en van zodra we een categorie (type = 1) herkennen we dezelfde functie opnieuw aanroepen.

Het onderstaande voorbeeld is in het PHP framework CodeIgniter geschreven, maar programmeurs met enige kennis van object-georiënteerd programmeren volgens het MVC model zullen de structuur snel begrijpen.

Controller:

function delete_category() {
 
     // Get the id
     $id = $this->input->post('id');
 
     // Create array to store the ids
     $ids_array = array();
 
     // Create a var to limit the number of queries
     $queries = 0;
 
     // Put the childnodes in the ids array
     $ids = $this->items_model->get_childnodes( $id, $ids_array, $queries );
 
     // If we didn't exceed the number of queries
     if ( $ids ) {
 
          // Don't forget to push the parent id in the array
          $ids_array[] = $id;
 
          // Delete the actual items on the server
          if ( ! $this->items_model->delete_items($ids_array) ) {
 
               // If FALSE, throw an error to JS
               echo 'something went wrong';
 
          }
 
     } else {
 
          // Throw error
          echo 'too deep';
 
     }
 
}

Model 1: get_childnodes

function get_childnodes ( $id, $child_ids, $queries ) {
 
     // Create the query
     $q = "SELECT id, type FROM items WHERE parent_id = ?";
 
     // Execute the query
     $sql = $this->db->query($q, array($id));
 
     // Check the number of executed queries
     if ( $queries < 25 ) {
 
          // Increase the number of queries with 1
          $queries++;
 
          // Check if there is a result
          if ( $sql->num_rows() > 0 ) {
 
               // Put the result in the $items object
               $items = $sql->result();
 
               // Loop through the result
               foreach ( $items as $item ) {
 
                    // If the item type is a folder
                    if ( $item->type == 0 ) {
 
                         // Recursively call the 'get_childnodes' function and merge the arrays together
                        $child_ids = array_merge($child_ids, $this->items_model->get_childnodes( $item->id, $child_ids, $queries ));
 
                    }
 
                    // Push the item id in the array
                    $child_ids[] = $item->id;
 
               }
 
          }
 
          // Finally return the array
          return $child_ids;
 
     }
 
     // Return FALSE if the number of queries exceeded the number of queries
     return FALSE;
 
}

Model 2: delete_items

function delete_items ( $ids_array ) {
 
     // Create the query
     $q = "DELETE FROM items WHERE id IN (" . implode(',', $ids_array) . ")";
 
     // Execute the query
     if ( $this->db->query($q) ) {
 
          return TRUE;
 
     }
 
     return FALSE;
 
}

Je kan ook een recursieve functie gebruiken om gewoon de hele folder structuur op te halen uit de database en te weergeven op een webpagina.

Interessante links:

copyright 2011 agmino web solutions