1:N Abfrage

[rapoo]

Hey, hab mich hier gerade erst angemeldet und hoffe das hier jmd so nett ist und mir helfen wird.

Ich stehe vor einer etwas kniffeligen mySQL Abfrage.

Ich habe 2 Tabellen mit einer 1 :n Beziehung.

1 Datensatz aus Tabelle 1 kann bis zu 6 Datensätzen aus Tabelle 2 besitzen.

Oder umgekehrt

Bis zu 6 Datensätze aus Tabelle 2 beziehen sich auf 1 Datensatz aus Tabelle 1.

Tab1 - artikel
artikelNr - bezeichnung - preis
123 - kekse - 0.99
124 - chips - 1.99

Tab2 - artikel_attribute
id - artikelNr - attribut - wert
1 - 123 - gewicht - 100g
2 - 123 - geschmack - schoko
3 - 123 - brennwert - 85kcal
...
7 - 124 - hersteller - chio
8 - 124 - sorte - red paprika


in tab2 sind selbst definierte Attribute der Artikel gespeichert.

mein ziel ist es, einen Artikel mit allen dazugehörigen Attributen abzufragen.

bisher bin ich soweit das meine Ausgabe wie folgt aussieht:
Abfrage
SELECT * FROM artikel art, artikel_attribute att WHERE att.artikelNr = art.artikelNr;
Ausgabe
[123 - kekse - 0.99]+[1 - 123 - gewicht - 100g]
[123 - kekse - 0.99]+[2 - 123 - geschmack - schoko]
[123 - kekse - 0.99]+[3 - 123 - brennwert - 85kcal]
... usw

das problem
wenn ich 10 artikel abfrage, bekomme ich ~60 zeilen, es sollten aber nach möglichkeit 10 zeilen bleiben (1 artikel + 6 attribute / je zeile).

also in etwa sowas:
Abfrage
?
soll Ausgabe
[artikel] + [attribute1] + [attribute2] + [attribute3] ...

[123 - kekse - 0.99]+[1 - 123 - gewicht - 100g]+[2 - 123 - geschmack - schoko]+[3 - 123 - brennwert - 85kcal]+... usw

hab noch viel platz im kopf und will wissen wies richtig gemacht wird

p.s.

momentan habe ich es mit 2 abfragen (3 schleifen) über PHP gelöst, was aber extrem *besch...* langsam ist.

 

[akretschmer]

Hey, hab mich hier gerade erst angemeldet und hoffe das hier jmd so nett ist und mir helfen wird.

Ich stehe vor einer etwas kniffeligen mySQL Abfrage.

test=*# select * from art;
 id  |  bez  | preis
-----+-------+-------
 123 | kekse |    99
 124 | ships |   199
(2 rows)

Time: 0,183 ms
test=*# select * from att;
 id | art |    att    |  wert
----+-----+-----------+--------
  1 | 123 | gewicht   | 100g
  2 | 123 | geschmack | schoko
  3 | 124 | brennwert | 85kcal
(3 rows)

Time: 0,182 ms
test=*# select id, bez, array_to_string(array_agg(gewicht),''), array_to_string(array_agg(geschmack),''), array_to_string(array_agg(brennwert),'') from (select a.id, a.bez, case when b.att='gewicht' then b.wert else '' end as gewicht, case when b.att='geschmack' then b.wert else '' end as geschmack, case when b.att='brennwert' then b.wert else '' end as brennwert from art a left join att b on a.id=b.art) foo group by id, bez;
 id  |  bez  | array_to_string | array_to_string | array_to_string
-----+-------+-----------------+-----------------+-----------------
 123 | kekse | 100g            | schoko          |
 124 | ships |                 |                 | 85kcal
(2 rows)

Nicht wirklich dramatisch schwer, oder?

Ist aber, wie bei mir üblich, in PostgreSQL gemacht. Anpassung an MySQL überlasse ich Dir zur Übung ;-)
(oder zur Strafe für die MySQL-Nutzung...)

Hinweis noch:

damit das nicht 'knallt', solltest Du die möglichen Attribute wirklich auf die zulässigen Werte begrenzen, z.B. via einer extra Tabelle, welche diese enthält (als primary key) und in der Attributtabelle nur mit Fremdschlüsseln darauf verweisen.

Also Bonus könntest Du einen TRIGGER auf diese Tabelle mit den möglichen Attributen machen, der bei Änderung obige Abfrage als VIEW neu erstellt. Mal so als Fleißaufgabe für Dich ;-)

Andreas

 

[rapoo]

Danke für deine Antwort akretschmer!

array_agg(gewicht)

case when b.att='gewicht' then b.wert

array_agg(geschmack)

case when b.att='geschmack' then b.wert

Verbessere mich wenn ich mich irre, aber das sieht aus als müssten die Attribute-Bezeichnung im Voraus bekannt sein. Das ist aber nicht der fall. Während ein Brett z.b. die Attribute Höhe, Breite, Stärke besitzt hat ein Auto die Attribute Kraftstoff, PS, Farbe.

Was mir noch auffällt, die 3 letzten Spalten heißen alle "array_to_string" ? Ist es möglich das da dann der Attribute-Bezeichner aus der Tab2 steht?

id  |  bez  | gewicht        | geschmack      | brennwert
-----+-------+-----------------+-----------------+-----------------
123 | kekse | 100g            | schoko          |
124 | ships |                |                | 85kcal

 

[akretschmer]

Danke für deine Antwort akretschmer!


Verbessere mich wenn ich mich irre, aber das sieht aus als müssten die Attribute-Bezeichnung im Voraus bekannt sein. Das ist aber nicht der fall. Während ein Brett z.b. die Attribute Höhe, Breite, Stärke besitzt hat ein Auto die Attribute Kraftstoff, PS, Farbe.

Ja, alle MÖGLICHEN müssen bekannt sein. Wenn Du bisher nur Bretter gebohrt, ähm verkauft hast und nun Autos mit neuen Attributen ist die Abfrage neu zu schreiben. Das ist der Teil mit dem TRIGGER-Bonus. Es gibt (bei PostgreSQL) auch noch ein Contrib-Paket 'tablefunc', was solche Dinge dynamischer macht. http://www.postgresql.org/docs/9.2/interactive/tablefunc.html

Was mir noch auffällt, die 3 letzten Spalten heißen alle "array_to_string" ? Ist es möglich das da dann der Attribute-Bezeichner aus der Tab2 steht?

id  |  bez  | gewicht        | geschmack      | brennwert
-----+-------+-----------------+-----------------+-----------------
123 | kekse | 100g            | schoko          |
124 | ships |                |                | 85kcal

Ja, Du kannst den Spalten Aliasnamen geben, aus "array_to_string(array_agg(gewicht),'')," entsprechend z.B. "array_to_string(array_agg(gewicht),'') as gewicht," schreiben. Hatte ich vergessen zu machen.

Andreas

 

[rapoo]

Hm vielen Dank Andreas!
Immerhin hab ich jetzt wieder einiges gelernt und mich in PostgreSQL eingelesen - sowas macht mir riesig Spaß

Ich bin mir nur noch etwas unsicher ob dieser Weg der einfachste ist und ob es das richtige ist, denn
a) die Anzahl der Artikel-Attributen ist unbekannt.
b) die Attribute-Bezeichnungen sind unbekannt.
c) die Handhabung in PHP wird dadurch auch etwas riesig.

Eigentlich sind 1:n Beziehungen total häufig, da wird sicher ne total Simple Lösung geben.
Tatsächlich ist die Welt voll von 1:n vermaledeites ding!

 

[akretschmer]

Hm vielen Dank Andreas!
Immerhin hab ich jetzt wieder einiges gelernt und mich in PostgreSQL eingelesen - sowas macht mir riesig Spaß

Ich bin mir nur noch etwas unsicher ob dieser Weg der einfachste ist und ob es das richtige ist, denn
a) die Anzahl der Artikel-Attributen ist unbekannt.
b) die Attribute-Bezeichnungen sind unbekannt.
c) die Handhabung in PHP wird dadurch auch etwas riesig.

Eigentlich sind 1:n Beziehungen total häufig, da wird sicher ne total Simple Lösung geben.
Tatsächlich ist die Welt voll von 1:n vermaledeites ding!

Ja, solche EAV-Modelle sind a bissl sperrig, und Zeilen-zu-Spalten-machen halt auch. Das mag in manchen Anwendungen ja sinnvoll sein (und z.B. Magento als Shopsoftware nutzt das auch), ist aber nicht so einfach zu handhaben wie das gute alte relationale Modell ;-)

Deine Punkte a) bis c) kann ich nachvollziehen... vielleicht doch besser bei ganz normalen Tabellen und Abfragen bleiben?


Andreas

 

[ukulele]

Bei maximal 6 Atributen gäbe es noch eine alternative Möglichkeit die nur eben nicht so dynamisch ist.

SELECT    a.id,
        a.bez,
        a.preis,
        t1.att,
        t1.wert,
        t2.att,
        t2.wert,
        t3.att,
        t3.wert
        t4.att,
        t4.wert,
        t5.att,
        t5.wert,
        t6.att,
        t6.wert
FROM    art a
LEFT JOIN att t1 ON t1.art = a.id AND t1.id = 1
LEFT JOIN att t1 ON t1.art = a.id AND t1.id = 2
LEFT JOIN att t1 ON t1.art = a.id AND t1.id = 3
LEFT JOIN att t1 ON t1.art = a.id AND t1.id = 4
LEFT JOIN att t1 ON t1.art = a.id AND t1.id = 5
LEFT JOIN att t1 ON t1.art = a.id AND t1.id = 6