Consultes recursives: bikeshop

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

Les taules recursives són les que tenen una clau forànea que apunta a ella mateixa. És molt típic per establir relacions de pares i fills. Però no només es limiten a dos nivells (pares i fills), sinó que poden créixer fins a tenir un arbre amb moltes branques i subbranques.

Per exemple, les pots trobar en el CMS Wordpress, per definir les categories i subcategories (que poden tenir més nivells).

En les nostres bases de dades de treball les trobem a bikeshop.CATEGORIA i HR.employees.

Base de dades bikeshop

Mirem la tala CATEGORIA:

select id_cat, categoria, id_parent from CATEGORIA

+--------+-----------------------------------------------+-----------+
| id_cat | categoria                                     | id_parent |
+--------+-----------------------------------------------+-----------+
|      1 | Carretera                                     |      NULL |
|      2 | MTB                                           |      NULL |
|      3 | E-BIKE                                        |      NULL |
|      4 | BMX                                           |      NULL |
|      5 | CIUDAD                                        |      NULL |
|      6 | Bicicletas                                    |         1 |
|      7 | Bicicletas de Carrera                         |         6 |
|      8 | Bicicletas de Gravel                          |         6 |
|      9 | Bicicletas de Ciclocross                      |         6 |
|     10 | Bicicletas Contrarreloj                       |         6 |
|     11 | Bicicletas de pista                           |         6 |
...

173 files

Volem visualitzar 2 nivells:

select C1.id_cat, C1.categoria, C1.id_parent, C2.id_cat, C2.categoria from CATEGORIA C1
INNER JOIN CATEGORIA C2
ON C1.id_parent=C2.id_cat;

+--------+-----------------------------------------------+-----------+--------+-----------------------------------+
| id_cat | categoria                                     | id_parent | id_cat | categoria                         |
+--------+-----------------------------------------------+-----------+--------+-----------------------------------+
|      6 | Bicicletas                                    |         1 |      1 | Carretera                         |
|      7 | Bicicletas de Carrera                         |         6 |      6 | Bicicletas                        |
|      8 | Bicicletas de Gravel                          |         6 |      6 | Bicicletas                        |
|      9 | Bicicletas de Ciclocross                      |         6 |      6 | Bicicletas                        |
|     10 | Bicicletas Contrarreloj                       |         6 |      6 | Bicicletas                        |
|     11 | Bicicletas de pista                           |         6 |      6 | Bicicletas                        |
|     12 | Bicicletas para mujeres                       |         6 |      6 | Bicicletas                        |
|     13 | Bicicletas Niños                              |         6 |      6 | Bicicletas                        |
|     14 | Bicicletas eléctricas                         |         6 |      6 | Bicicletas                        |
|     15 | Cuadros                                       |         1 |      1 | Carretera                         |
|     16 | Cuadros de carretera                          |        15 |     15 | Cuadros                           |
|     17 | Cuadros de Gravel                             |        15 |     15 | Cuadros                           |
|     18 | Horquillas                                    |        15 |     15 | Cuadros                           |
...

168 files

Hem perdut 5 categories. Amb un LEFT OUTER JOIN tornem a recuperar els 173 valors (les categories que no tenen pare)

select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1
LEFT OUTER JOIN CATEGORIA C2
ON C1.id_parent=C2.id_cat;

+-----------------------------------+-----------------------------------------------+
| cat_pare                          | cat_fill                                      |
+-----------------------------------+-----------------------------------------------+
| NULL                              | Carretera                                     |
| NULL                              | MTB                                           |
| NULL                              | E-BIKE                                        |
| NULL                              | BMX                                           |
| NULL                              | CIUDAD                                        |
| Carretera                         | Bicicletas                                    |
| Bicicletas                        | Bicicletas de Carrera                         |
| Bicicletas                        | Bicicletas de Gravel                          |
| Bicicletas                        | Bicicletas de Ciclocross                      |
| Bicicletas                        | Bicicletas Contrarreloj                       
...

173 files

Volem filtrar el que penja de Bicicletas > CIUDAD > transmisión filtrem:

select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1
LEFT OUTER JOIN CATEGORIA C2
ON C1.id_parent=C2.id_cat
WHERE C2.categoria='Transmisión';

+--------------+---------------------------------------+
| cat_pare     | cat_fill                              |
+--------------+---------------------------------------+
| Transmisión  | Cables y fundas                       |
| Transmisión  | Cassettes y cadenas                   |
| Transmisión  | Cambios y desviadores                 |
| Transmisión  | Manetas                               |
| Transmisión  | Pedaliers                             |
| Transmisión  | Pedales                               |
| Transmisión  | Transmisión eléctrica                 |
...

33 files

Ho fem malament perquè la categoria Transmisión no és única.

33 files perquè hem trobat:

  • Bicicletas > carretera > Transmisión
  • Bicicletas > MTB > Transmisión
  • Bicicletas > BMX > BMX FreeStyle > Transmisión
  • Bicicletas > CIUDAD > Transmisión

Volem doncs ara visualitzar els 3 nivells

select C1.id_cat, C1.categoria, C1.id_parent, C2.id_cat, C2.categoria, C3.id_cat, C3.categoria from 
((CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat)
LEFT OUTER JOIN CATEGORIA C3 ON C2.id_parent=C3.id_cat);

+--------+-----------------------------------------------+-----------+--------+-----------------------------------+--------+---------------+
| id_cat | categoria                                     | id_parent | id_cat | categoria                         | id_cat | categoria     |
+--------+-----------------------------------------------+-----------+--------+-----------------------------------+--------+---------------+
|      1 | Carretera                                     |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      2 | MTB                                           |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      3 | E-BIKE                                        |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      4 | BMX                                           |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      5 | CIUDAD                                        |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      6 | Bicicletas                                    |         1 |      1 | Carretera                         |   NULL | NULL          |
|      7 | Bicicletas de Carrera                         |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|      8 | Bicicletas de Gravel                          |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|      9 | Bicicletas de Ciclocross                      |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     10 | Bicicletas Contrarreloj                       |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     11 | Bicicletas de pista                           |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     12 | Bicicletas para mujeres                       |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     13 | Bicicletas Niños                              |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     14 | Bicicletas eléctricas                         |         6 |      6 | Bicicletas                        |      1 | Carretera     |
...

173 files
select C3.categoria cat_avi, C2.categoria cat_pare, C1.categoria cat_fill from 
((CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat)
LEFT OUTER JOIN CATEGORIA C3 ON C2.id_parent=C3.id_cat)
WHERE C3.categoria='CIUDAD' AND C2.categoria='Transmisión';

+---------+--------------+---------------------------------------+
| cat_avi | cat_pare     | cat_fill                              |
+---------+--------------+---------------------------------------+
| CIUDAD  | Transmisión  | Cables y fundas                       |
| CIUDAD  | Transmisión  | Cubrecadenas                          |
| CIUDAD  | Transmisión  | Cassettes                             |
| CIUDAD  | Transmisión  | Cadenas                               |
| CIUDAD  | Transmisión  | Mandos de cambio                      |
| CIUDAD  | Transmisión  | Cambios                               |
| CIUDAD  | Transmisión  | Pedaliers, Cajas del pedalier, Platos |
+---------+--------------+---------------------------------------+

Base de dades HR. Taula employees

En la base de dades HR' tenim la taula employees on també tenim una informació recursiva: d'un empleat sabem qui és el seu jefe, que a l'hora pot ser empleat d'un altre jefe.

mysql> DESC employees;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| employee_id   | int          | NO   | PRI | NULL    | auto_increment |
| first_name    | varchar(20)  | YES  |     | NULL    |                |
| last_name     | varchar(25)  | NO   |     | NULL    |                |
...
| manager_id    | int          | YES  | MUL | NULL    |                |
+---------------+--------------+------+-----+---------+---------------
select employee_id, CONCAT(first_name,' ',last_name), manager_id from employees;

+-------------+----------------------------------+------------+
| employee_id | CONCAT(first_name,' ',last_name) | manager_id |
+-------------+----------------------------------+------------+
|         100 | Steven King                      |       NULL |
|         101 | Neena Kochhar                    |        100 |
|         102 | Lex De Haan                      |        100 |
|         103 | Alexander Hunold                 |        102 |
|         104 | Bruce Ernst                      |        103 |
|         105 | David Austin                     |        103 |
|         106 | Valli Pataballa                  |        103 |
|         107 | Diana Lorentz                    |        103 |
...

40 files

Igual que abans, podem mirar la llista d'empleats i jefes:

select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat, e1.manager_id, e2.employee_id, CONCAT(e2.first_name,' ',e2.last_name) jefe from employees e1
LEFT OUTER JOIN employees e2
ON e1.manager_id=e2.employee_id;

+-------------+-------------------+------------+-------------+-------------------+
| employee_id | empleat           | manager_id | employee_id | jefe              |
+-------------+-------------------+------------+-------------+-------------------+
|         100 | Steven King       |       NULL |        NULL | NULL              |
|         101 | Neena Kochhar     |        100 |         100 | Steven King       |
|         102 | Lex De Haan       |        100 |         100 | Steven King       |
|         103 | Alexander Hunold  |        102 |         102 | Lex De Haan       |
|         104 | Bruce Ernst       |        103 |         103 | Alexander Hunold  |
|         105 | David Austin      |        103 |         103 | Alexander Hunold  |
|         106 | Valli Pataballa   |        103 |         103 | Alexander Hunold  |
|         107 | Diana Lorentz     |        103 |         103 | Alexander Hunold  |
|         108 | Nancy Greenberg   |        101 |         101 | Neena Kochhar     |
...

40 files

Quins són els subordinats del Alexander Hunold?

select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat from employees e1
LEFT OUTER JOIN employees e2
ON e1.manager_id=e2.employee_id
WHERE CONCAT(e2.first_name,' ',e2.last_name)='Alexander Hunold';

+-------------+-----------------+
| employee_id | empleat         |
+-------------+-----------------+
|         104 | Bruce Ernst     |
|         105 | David Austin    |
|         106 | Valli Pataballa |
|         107 | Diana Lorentz   |
+-------------+-----------------+
4 rows in set (0,00 sec)

Igual que abans, podem anar a visualitzar 3 nivells:

select CONCAT(e3.first_name,' ',e3.last_name) superjefe, CONCAT(e2.first_name,' ',e2.last_name) jefe, CONCAT(e1.first_name,' ',e1.last_name) empleat from
((employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id)
LEFT OUTER JOIN employees e3 ON e2.manager_id=e3.employee_id);

Informe de tothom que penja de Lex De Haan (aquesta persona pot actuar com a jefe o com a superjefe):

select CONCAT(e3.first_name,' ',e3.last_name) superjefe, CONCAT(e2.first_name,' ',e2.last_name) jefe, CONCAT(e1.first_name,' ',e1.last_name) empleat from
((employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id)
LEFT OUTER JOIN employees e3 ON e2.manager_id=e3.employee_id)
WHERE CONCAT(e2.first_name,' ',e2.last_name)='Lex De Haan' OR CONCAT(e3.first_name,' ',e3.last_name)='Lex De Haan';

+-------------+------------------+------------------+
| superjefe   | jefe             | empleat          |
+-------------+------------------+------------------+
| Steven King | Lex De Haan      | Alexander Hunold |
| Lex De Haan | Alexander Hunold | Bruce Ernst      |
| Lex De Haan | Alexander Hunold | David Austin     |
| Lex De Haan | Alexander Hunold | Valli Pataballa  |
| Lex De Haan | Alexander Hunold | Diana Lorentz    |
+-------------+------------------+------------------+
5 rows in set (0,00 sec)

creat per Joan Quintana Compte, febrer 2022