[MySQL] aiuto select

[Skywalker87]

Ciao!

Ho le seguenti tabelle:

CREATE TABLE Pizza
(
	Codice		SMALLINT UNSIGNED	PRIMARY KEY	AUTO_INCREMENT,
	Nome		VARCHAR(50)		NOT NULL,
	Prezzo		DECIMAL(3,2)		NOT NULL,
	Data		DATE			NOT NULL,
	Preferenze	SMALLINT UNSIGNED	NOT NULL	DEFAULT 0
) ENGINE = InnoDB;

CREATE TABLE Ingrediente
(
	Nome		VARCHAR(50)		PRIMARY KEY
) ENGINE = InnoDB;

CREATE TABLE Composizione
(
	Pizza		SMALLINT UNSIGNED,
	Ingrediente	VARCHAR(50),
	FOREIGN KEY (Pizza)		REFERENCES Pizza(Codice) 
					ON DELETE CASCADE ON UPDATE CASCADE, 
	FOREIGN KEY (Ingrediente)	REFERENCES Ingrediente(Nome) 
					ON DELETE CASCADE ON UPDATE CASCADE, 
	PRIMARY KEY (Pizza, Ingrediente)
) ENGINE = InnoDB;

Vorrei scrivere un'interrogazione per poter estrarre solo le pizze con determinati ingredienti. Il numero degli ingredienti su cui filtrare le pizze non è conosciuto a priori, e ogni pizza del risultato deve essere composta da tutti gli ingredienti scelti.


Esempio, se le tabelle avessero delle istanze tali che:

mysql> SELECT P.Codice, P.Nome, P.Prezzo, P.Data, P.Preferenze, C.Ingrediente FROM Pizza P, Composizione C WHERE P.Codice = C.Pizza;
+--------+-------------+--------+------------+------------+------------------+
| Codice | Nome        | Prezzo | Data       | Preferenze | Ingrediente      |
+--------+-------------+--------+------------+------------+------------------+
|      1 | Margherita  |   4.50 | 2011-12-14 |          0 | Mozzarella       |
|      1 | Margherita  |   4.50 | 2011-12-14 |          0 | Pomodoro         |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Carciofi         |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Funghi           |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Mozzarella       |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Pomodoro         |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Prosciutto cotto |
+--------+-------------+--------+------------+------------+------------------+

Allora il risultato dell'interrogazione sugli ingredienti Pomodoro e Carciofi sarebbe soltanto la pizza Capricciosa.


Chi mi da qualche spunto per scrivere questa SELECT?

Grazie mille!

[Skywalker87]

Ho trovato una soluzione.

Innanzi tutto definisco la seguente vista:

CREATE VIEW ElencoPizzeIngredienti AS
SELECT P.Codice, P.Nome, P.Prezzo, P.Data, P.Preferenze, C.Ingrediente
FROM Pizza P, Composizione C
WHERE P.Codice = C.Pizza;

Se i dati fossero tali che:

mysql> SELECT * FROM ElencoPizzeIngredienti;
+--------+-------------+--------+------------+------------+------------------+
| Codice | Nome        | Prezzo | Data       | Preferenze | Ingrediente      |
+--------+-------------+--------+------------+------------+------------------+
|      1 | Margherita  |   4.50 | 2011-12-14 |          0 | Mozzarella       |
|      1 | Margherita  |   4.50 | 2011-12-14 |          0 | Pomodoro         |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Carciofi         |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Funghi           |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Mozzarella       |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Pomodoro         |
|      2 | Capricciosa |   6.50 | 2011-12-14 |          0 | Prosciutto cotto |
|      3 | Bresaola    |   7.50 | 2011-12-16 |          0 | Bresaola         |
|      3 | Bresaola    |   7.50 | 2011-12-16 |          0 | Grana            |
|      3 | Bresaola    |   7.50 | 2011-12-16 |          0 | Mozzarella       |
|      3 | Bresaola    |   7.50 | 2011-12-16 |          0 | Pomodoro         |
|      3 | Bresaola    |   7.50 | 2011-12-16 |          0 | Rucola           |
|      4 | Marinara    |   4.00 | 2011-12-18 |          0 | Aglio            |
|      4 | Marinara    |   4.00 | 2011-12-18 |          0 | Pomodoro         |
+--------+-------------+--------+------------+------------+------------------+

Con la seguente interrogazione estraggo solo le pizze i quali ingredienti comprendono Pomodoro e Carciofi:

SELECT *
FROM ElencoPizzeIngredienti
WHERE Codice IN (SELECT Codice
                 FROM ElencoPizzeIngredienti
                 WHERE Ingrediente = 'Pomodoro' OR Ingrediente = 'Carciofi'
                 GROUP BY Codice
                 HAVING COUNT(DISTINCT Ingrediente) = 2);

Ovviamente la ricerca può essere estesa ad un numero di ingredienti qualsiasi, basta aggiungere (o togliere) una condizione Ingrediente = 'IngredienteDaFiltrare' nella clausola WHERE della SELECT interna e aggiornare il numero degli ingredienti (COUNT(DISTINCT Ingrediente)) su cui si effettua la selezione.


Ora ciò che voglio fare è automatizzare il tutto con una PROCEDURE. MySQL non permette il passaggio di array, perciò passerò l'elenco degli ingredienti tramite un campo TEXT correttamente formattato; ad esempio separando gli ingredienti dal carattere punto.

La procedure deve compiere due operazioni sul parametro in ingresso:

1. calcolare il numero degli ingredienti
2. costruire la clausola WHERE della SELECT interna come nell'esempio

L'operazione 1 non è un problema, supponendo (come già accennato) di formattare il parametro in ingresso separando gli ingredienti per mezzo del carattere punto, il seguente codice setta la variabile NumeroIngredienti al valore 3:

DECLARE Ingredienti TEXT;
DECLARE NumeroIngredienti SMALLINT;

SET Ingredienti = 'Pomodoro.Mozzarella.Carciofi';

SELECT 1 + CHAR_LENGTH(Ingredienti) - CHAR_LENGTH(REPLACE(Ingredienti, '.', '')) INTO NumeroIngredienti;

Il problema nasce con l'operazione 2, il parametro Ingredienti deve essere trasformato nella stringa Ingrediente = 'Ingrediente1' OR Ingrediente = 'Ingrediente2' OR ..., e fin qui nulla di difficile, il vero ostacolo è far riconoscere la stringa generata come parametro della clausola WHERE. Infatti, supponendo di avere una stringa già correttamente formattata, MySQL non interpreta come vorrei (evidentemente per ovvi motivi a me ignari) la seguente istruzione:

DECLARE Ingredienti TEXT;

SET Ingredienti = 'Ingrediente = \\'Pomodoro\\' OR Ingrediente = \\'Mozzarella\\' OR \\'Carciofi\\'';

SELECT * FROM ElencoPizzeIngredienti WHERE Ingredienti;

In particolare mi restituisce un "empty set".

Qualcuno ha qualche soluzione al problema oppure qualche altra strada per raggiungere il mio scopo?

Grazie mille!

[optime]

puoi usare

... where ingrediente in ('pomodoro', 'mozzarella', 'caciocavallo') ...


la IN funziona come la OR

[Skywalker87]

Perfetto! Grazie al tuo consiglio ho risolto.

Ecco la procedura completa:

DROP PROCEDURE SelezionaPizzeIngredienti;
DELIMITER //
CREATE PROCEDURE SelezionaPizzeIngredienti(IN Ingredienti TEXT, IN Ordinamento VARCHAR(10))
BEGIN
     DECLARE NumeroIngredienti SMALLINT;
     DECLARE Ingrediente VARCHAR(50);
     DECLARE i SMALLINT DEFAULT 1;
     CREATE TEMPORARY TABLE IngredientiTemp ( Nome VARCHAR(50) PRIMARY KEY ) ENGINE = InnoDB;
     SELECT 1 + CHAR_LENGTH(Ingredienti) - CHAR_LENGTH(REPLACE(Ingredienti, '.', '')) INTO NumeroIngredienti;
     ciclo: WHILE i <= NumeroIngredienti DO
          SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Ingredienti, '.', i), '.', -1) INTO Ingrediente;
          INSERT INTO IngredientiTemp VALUES(Ingrediente);
          SET i = i + 1;
     END WHILE ciclo;
     IF Ordinamento = 'Nome' THEN
          SELECT *
          FROM ElencoPizzeIngredienti E1
          WHERE E1.Codice IN (SELECT E2.Codice
                              FROM ElencoPizzeIngredienti E2
                              WHERE E2.Ingrediente = ANY (SELECT I.Nome FROM IngredientiTemp I)
                              GROUP BY E2.Codice HAVING COUNT(DISTINCT E2.Ingrediente) = NumeroIngredienti)
                              ORDER BY E1.Nome, E1.Codice;
     ELSEIF Ordinamento = 'Preferenze' THEN
          SELECT *
          FROM ElencoPizzeIngredienti E1
          WHERE E1.Codice IN (SELECT E2.Codice
                              FROM ElencoPizzeIngredienti E2
                              WHERE E2.Ingrediente = ANY (SELECT I.Nome FROM IngredientiTemp I)
                              GROUP BY E2.Codice HAVING COUNT(DISTINCT E2.Ingrediente) = NumeroIngredienti)
                              ORDER BY E1.Preferenze, E1.Codice;
     END IF;
     DROP TABLE IngredientiTemp;
END; //
DELIMITER ;

La procedura crea una tabella temporanea in cui salva gli ingrediente da filtrare e tramite l'operatore = ANY (che equivale all'operatore IN che mi hai suggerito) seleziona solo gli ingrediente appartenenti alla tabella temporanea. Infine la procedura cancella la tabella temporanea.

Grazie mille per l'aiuto!

Ora ho un altro piccolo problema: [MySQL] Procedura e ritorno di errori

Ti va di darmi ancora una mano? Grazie!