Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

1:N Abfrage

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von rapoo, 13 März 2013.

  1. rapoo

    rapoo Neuer Benutzer

    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

    p.s.

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

    akretschmer Datenbank-Guru

    Code:
    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
     
  3. rapoo

    rapoo Neuer Benutzer

    Danke für deine Antwort akretschmer!

    Code:
    array_agg(gewicht)
    Code:
    case when b.att='gewicht' then b.wert
    Code:
    array_agg(geschmack)
    Code:
    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?
    Code:
    id  |  bez  | gewicht        | geschmack      | brennwert
    -----+-------+-----------------+-----------------+-----------------
    123 | kekse | 100g            | schoko          |
    124 | ships |                |                | 85kcal
     
  4. akretschmer

    akretschmer Datenbank-Guru

    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



    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
     
  5. rapoo

    rapoo Neuer Benutzer

    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 :D vermaledeites ding!
     
  6. akretschmer

    akretschmer Datenbank-Guru


    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
     
  7. ukulele

    ukulele Datenbank-Guru

    Bei maximal 6 Atributen gäbe es noch eine alternative Möglichkeit die nur eben nicht so dynamisch ist.
    Code:
    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
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden