SQL Query für Cocktailmaschine gesucht :)

[padii]

Hi!

ich benötige etwas Hilfe für eine DB-Abfrage. Sollte eigentlich einfach machbar sein, aber ich verzweifle gerade daran eine passende Abfrage zu finden.
Die Struktur der DB sieht folgendermaßen aus:



Das ganze wird für eine selbstentwickelte Cocktail-Misch-Maschine gebraucht. Die Cocktailmaschine hat insgesamt 8 Eingänge für verschiedene Mischgetränke, Alkohol, Liköre, etc.
Am Anfang kommt man auf ein Webinterface an dem man festlegt was man wo stehen hat. Zum Beispiel stehen an 2 Eingängen Cola Flasche, am 3. steht Gin, am 4. Vodka, am 5. Whisky, usw.

Daten in der Tabelle besteht_aus sehen z.B. so aus (sind zum Testen nur Longdrinks eingetragen):

1. Zeile ist die Cocktailbezeichnung, 2. die Zutat, 3. die Menge in Prozent.
> heisst hier bspw. ein Barcardi Cola besteht aus 20% Barcardi und 80% Cola.

Ich will anhand dieser Tabelle eine Abfrage basteln die ich später auch in PHP variabel machen kann mit bis zu 8 verschiedenen Zutaten die ich habe und er mir alle möglichen Drinks anzeigt die daraus möglich sind. Jedoch keine Drinks zu denen die Zutaten fehlen.
Durch das Skript kann es passieren das man z.B. an mehreren Eingängen die gleichen Zutaten stehen hat, aber das ändert an der Abfrage wahrscheinlich nichts.

Vielen Dank für die Hifle!

 

[akretschmer]

ch will anhand dieser Tabelle eine Abfrage basteln die ich später auch in PHP variabel machen kann mit bis zu 8 verschiedenen Zutaten die ich habe und er mir alle möglichen Drinks anzeigt die daraus möglich sind. Jedoch keine Drinks zu denen die Zutaten fehlen.

Was ist jetzt die konkrete Frage?

Wenn Du es sauber machen willst wirst Du mehrere Tabellen brauchen, die untereinander mit Fremdschlüsseln verbunden sind. Wie ist denn Dein bisheriger Ansatz?

 

[padii]

Frage steht ganz unten im letzten Absatz. Ich suche eine Abfrage die mir anhand von (mehreren) Zutaten ausgibt welche Drinks möglich sind. Habe ich z.B. 3 Zutaten als Input solle er mir aus diesen Zutaten alle Drinks ausgeben die möglich sind, aber keine Drinks bei denen bspw. noch eine Zutat fehlt.

 

[akretschmer]

Frage steht ganz unten im letzten Absatz. Ich suche eine Abfrage die mir anhand von (mehreren) Zutaten ausgibt welche Drinks möglich sind. Habe ich z.B. 3 Zutaten als Input solle er mir aus diesen Zutaten alle Drinks ausgeben die möglich sind, aber keine Drinks bei denen bspw. noch eine Zutat fehlt.

Also, ich zeig mal eine Lösung.

Du hast

test=*# select * from besteht_aus ;
  rezept  |  zutat  | anteil
--------------+---------+--------
 Bacardi Cola | Bacardi |  20
 Bacardi Cola | Cola  |  80
 Gin Tonic  | Gin  |  30
 Gin Tonic  | Tonic  |  70
 Vodka Cola  | Vodka  |  30
 Vodka Cola  | Cola  |  70
(6 rows)

Time: 0,182 ms
test=*# select * from mixer ;
 id | zutat
----+-------
  1 | Cola
  3 | Gin
  5 | Tonic
(3 rows)

Daraus mache ich erst mal noch einen View, der alle Rezepte erst mal aggregiert, und eine extra Spalte hat, wo die Summe der Anteile steht. Später könnte man dies noch als Prüfkriterum nehmen, es muß ja immer 100 sein.

test=*# \d+ mix;
  View "public.mix"
 Column  |  Type  | Modifiers | Storage  | Description
---------+--------+-----------+----------+-------------
 rezept  | text  |  | extended |
 zutaten | text[] |  | extended |
 valid  | bigint |  | plain  |
View definition:
 SELECT besteht_aus.rezept,
  array_agg(besteht_aus.zutat) AS zutaten,
  sum(besteht_aus.anteil) AS valid
  FROM besteht_aus
  GROUP BY besteht_aus.rezept;

test=*# select * from mix;
  rezept  |  zutaten  | valid
--------------+----------------+-------
 Bacardi Cola | {Bacardi,Cola} |  100
 Vodka Cola  | {Vodka,Cola}  |  100
 Gin Tonic  | {Gin,Tonic}  |  100
(3 rows)

Nun schauen wie, was verfügbar ist:

test=*# select *, ist_da @> zutaten as verfügbar from mix cross join (select array_agg(zutat) as ist_da from mixer) foo ;
  rezept  |  zutaten  | valid |  ist_da  | verfügbar
--------------+----------------+-------+------------------+-----------
 Bacardi Cola | {Bacardi,Cola} |  100 | {Cola,Gin,Tonic} | f
 Vodka Cola  | {Vodka,Cola}  |  100 | {Cola,Gin,Tonic} | f
 Gin Tonic  | {Gin,Tonic}  |  100 | {Cola,Gin,Tonic} | t
(3 rows)

In der letzten Spalte ist nur bei Gin Tonic ein t, also TRUE, also verfügbar.


Das ist jetzt allerdings nicht in MySQL gemacht.

 

[padii]

Danke akretschmer für die Mühe!
Ich habe mal versucht eine Abfrage zu basteln. Leider komme ich noch nicht ganz zu dem Ziel.



Ich will gerne alles in einer Abfrage haben, da ich nicht weiß wie ich mit verschiedenen Abfragen später in PHP umgehen kann. Bin da wirklich noch ein echter Anfänger.
Die valid Prüfung erledige ich schon vor dem Eintragen in PHP ist also bereits alles auf 100

Was hier eigentlich nur noch fehlt wäre die Prüfung ob zutaten in verfuegbar vorhanden ist und dann genau diese ausgeben. Bei dir gibt er das schon perfekt aus mit f oder t, bekomme das aber in MySQL nicht hin. Die WHERE-Klausel kann ihrgendwie nicht mit GROUP_CONCAT umgehen. Eventuell kennt da jemand einen alternativen Weg?

Die Datenbankstruktur muss übrigens auch nicht so aussehen, bin für bessere Vorschläge natürlich offen.

 

[akretschmer]

Was hier eigentlich nur noch fehlt wäre die Prüfung ob zutaten in verfuegbar vorhanden ist und dann genau diese ausgeben. Bei dir gibt er das schon perfekt aus mit f oder t, bekomme das aber in MySQL nicht hin. Die WHERE-Klausel kann ihrgendwie nicht mit GROUP_CONCAT umgehen. Eventuell kennt da jemand einen alternativen Weg?

Du könntest die Anzahl der Zutaten zählen und dann zählen, wie viele davon am Mixer verfügbar sind. GROUP_CONCAT gibt Dir einen String, das array_agg ein ARRAY, und für Arrays gibt es halt auch Operatoren, die z.B. prüfen, ob ein Array innerhalb eines anderen abgebildet ist. Ist halt PostgreSQL, ein paar Lichtjahre besser als MySQL.

Die Datenbankstruktur muss übrigens auch nicht so aussehen, bin für bessere Vorschläge natürlich offen.

Auch die DB? Dann hast ja schon einen Vorschlag.