Ankündigung

Einklappen
Keine Ankündigung bisher.

Wie kann man die Selektion aus einer SQL-DB verhindern, wenn gewisse Werte vorliegen

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Wie kann man die Selektion aus einer SQL-DB verhindern, wenn gewisse Werte vorliegen

    Hi,
    ich habe eine normalisierte Datenbank, wo ich in einer übergeordneten Tabelle verschiedene Produkte habe und in einer untergeordneten Tabelle Eigenschaften, wobei jede Eigenschaft über einen Foreign Key mit dem Produkt in der übergeordneten Tabelle verbunden ist, wenn diese zutrifft. Das heißt, dass ein und dieselbe characteristic_id (= Foreign Key) in der untergeordneten Tabelle mehrfach vorkommen kann, wenn mehrere Eigenschaften auf ein Produkt zutreffen, z.B.


    Übergeordnete Haupttabelle (product_main_table):

    product_id product_name
    23 Apple
    24 Orange
    25 Strawberry



    Untergeordnete Tabelle (product_characteristics):

    characteristic_id product_name characteristic
    23 Apple green
    23 Apple sweet
    23 Apple small
    23 Apple american
    24 Orange orange
    24 Orange sourly
    24 Orange big
    24 Orange african
    25 Strawberry red
    25 Strawberry sweet


    Ich habe nun ein HTML-Formular zum Auslesen der Daten, wobei es auch die Möglichkeit geben soll, alle Produkte zu suchen, die gewisse Eigenschaften NICHT haben.
    Allerdings funktioniert das nicht so wie es soll, weil wenn ich folgende Abfrage eingebe:

    SELECT DISTINCT main.product_name FROM product_main_table main, product_characteristics prodchar WHERE prodchar.characteristic != 'sweet'

    wird apple, orange und strawberry ausgegeben, obwohl nur orange alleine richtig wäre. Die anderen beiden Produkte werden natürlich ausgewählt, weil sie auch andere Eigenschaften als 'sweet' haben, die dann selektioniert werden.
    Wie muss ich mein SELECT-Statement nun gestalten, damit ein Produkt nicht ausgewählt wird, wenn es auch nur eine einzige Eigenschaft hat, die nicht vorhanden sein soll?

    Vielen Dank für Eure Hilfe!


  • #2
    PostgreSQL?

    Falls ja: wir betrachten hier mal nur die zweite Tabelle.

    Code:
    test=*# select * from product_characteristics;
     id | characteristic
    ----+----------------
     23 | green
     23 | sweet
     23 | small
     23 | american
     24 | orange
     24 | sourly
     24 | big
     25 | red
     25 | sweet
    (9 Zeilen)
    
    test=*# select * from (select id, array_agg(characteristic) x from product_characteristics group by 1) foo where not (foo.x  @> array['sweet']);
     id |          x          
    ----+---------------------
     24 | {orange,sourly,big}
    (1 Zeile)
    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Kommentar


    • #3
      Deine Tabellen bzw. Schlüssel sind merkwürdig aufgebaut.
      In der "product" Tabelle sollte der Schlüssel die id sein, richtig?
      Dann gehört in deine andere Tabelle ein Fremdschlüssel mit eben jener id und die Spalte nennt sich dann am besten "product_id". characteristic_id hingegen sollte der Primärschlüssel sein und von der Datenbank (mit Auto Increment) gepflegt werden. Die Produktnamen haben dann in der 2. Tabelle nichts mehr verloren.

      Anbei sind Namenszusätze wie "main_table" meistens informationslos und somit überflüssig.

      Kommentar


      • #4
        Danke akretschmer für Deinen Lösungsvorschlag! Hab leider mit PostgreSQL null Erfahrung! Müsste die Lösung irgendwie auch in meine bestehende SELECT-Abfrage einfügen:

        $sql = "SELECT DISTINCT $selection FROM $tabelle WHERE $masterarray";

        wobei über das HTML-Formular Werte eingegeben werden, die dann in die Variablen $selection, $tabelle und $masterarray wandern und so letztendlich die Abfrage ergeben.

        Gibt es auch eine Möglichkeit, dieses Problem hier im WHERE-Statement zu lösen?

        Kommentar


        • #5
          Zitat von Greehorn17 Beitrag anzeigen
          Danke akretschmer für Deinen Lösungsvorschlag! Hab leider mit PostgreSQL null Erfahrung! Müsste die Lösung irgendwie auch in meine bestehende SELECT-Abfrage einfügen:

          $sql = "SELECT DISTINCT $selection FROM $tabelle WHERE $masterarray";

          wobei über das HTML-Formular Werte eingegeben werden, die dann in die Variablen $selection, $tabelle und $masterarray wandern und so letztendlich die Abfrage ergeben.

          Gibt es auch eine Möglichkeit, dieses Problem hier im WHERE-Statement zu lösen?
          Ja

          Nur zunächst mal lässt du das hier bleiben:

          $sql = "SELECT DISTINCT $selection FROM $tabelle WHERE $masterarray";

          Selbst, wenn du alles vorher absicherst... So arbeitet man einfach nicht. Sieht u.A.: http://php.net/manual/de/security.da...-injection.php

          Und zu deinem Query. Da würden mir spontan 3 Möglichkeiten einfallen, wie man sowas mit MySQL löst. Zunächst mal habe ich dein Schema korrigiert:

          products: id, name
          product_characteristics: product_id => (products.id), characteristic

          Queries:

          PHP-Code:
          SELECT
              p
          .id,
              
          p.name
          FROM
              
          (SELECT
                  p
          .id,
                  
          GROUP_CONCAT(pc.characteristic) AS characteristics
              FROM
                  products p
              INNER JOIN
                  product_characteristics pc ON pc
          .product_id=p.id
              GROUP BY
                  p
          .idt
          INNER JOIN
              products p ON p
          .id t.id
          WHERE
              NOT FIND_IN_SET
          ('red't.characteristics
          PHP-Code:
          SELECT
              p
          .id,
              
          p.name
          FROM
              products p
          WHERE
              p
          .id NOT IN
                  
          (SELECT
                      p
          .id
                  FROM
                      products p
                  LEFT JOIN
                      product_characteristics pc ON p
          .id pc.product_id AND pc.characteristic IN ('red''sweet')
                  
          WHERE
                      NOT ISNULL
          (pc.product_id)
                  
          GROUP BY
                      p
          .id
          PHP-Code:
          SELECT
              t
          .id,
              
          p.name
          FROM
              
          (SELECT
                  p
          .id,
                  
          COUNT(pc.product_id) AS exclude_count
              FROM
                  products p
              LEFT JOIN
                  product_characteristics pc ON p
          .id pc.product_id AND pc.characteristic IN ('red''sweet')
              
          GROUP BY
                  p
          .idt
          INNER JOIN
              products p ON p
          .id t.id
          WHERE
              t
          .exclude_count 
          Alle Lösungen haben eins gemein: Wenn es viele Daten werden, wird das langsam.

          Ohne subquery, dafür aber mit HAVING:

          PHP-Code:
          SELECT
              p
          .id,
              
          p.name,
              
          COUNT(pc.product_id) AS exclude_count
          FROM
              products p
          LEFT JOIN
              product_characteristics pc ON p
          .id pc.product_id AND pc.characteristic IN ('red''sweet')
          GROUP BY
              p
          .idp.name
          HAVING
              exclude_count 

          Kommentar


          • #6
            QUOTE]
            products: id, name
            product_characteristics: product_id => (products.id), characteristic
            [/QUOTE]

            In der Tabelle "product_characteristics" nur eine ID zum Produktnamen zu hinterlegen, macht das Ganze ja irgendwie nicht besser.

            Dann sucht man nach Apple, und bekommt dann entweder Apple->Green, oder Apple->big . . . usw.?

            Besser wäre doch, zum Produkt in "product_characteristics" spezifische Eigenschaften zu einem "Unterprodukt" zu hinterlegen.

            Beispiel:

            Tabelle "products":
            id|product
            1|Apple

            Tabelle "product_characteristics":
            product_id|product_sort|product_smell|product_size |product_color
            1|Granny Smith|sauer|90mm|green
            1|Braeburn|suess|100mm|red yellow

            Dann würde man nach einem Produkt suchen, zusätzlich nach Produktsorten die wiederum verschiedene Namen, Geschmäcker, Größen und Farben haben.

            Gruß
            Günni

            Kommentar


            • #7
              Zitat von Günni Beitrag anzeigen
              QUOTE]

              In der Tabelle "product_characteristics" nur eine ID zum Produktnamen zu hinterlegen, macht das Ganze ja irgendwie nicht besser.

              Dann sucht man nach Apple, und bekommt dann entweder Apple->Green, oder Apple->big . . . usw.?

              Besser wäre doch, zum Produkt in "product_characteristics" spezifische Eigenschaften zu einem "Unterprodukt" zu hinterlegen.

              Beispiel:

              Tabelle "products":
              id|product
              1|Apple

              Tabelle "product_characteristics":
              product_id|product_sort|product_smell|product_size |product_color
              1|Granny Smith|sauer|90mm|green
              1|Braeburn|suess|100mm|red yellow

              Dann würde man nach einem Produkt suchen, zusätzlich nach Produktsorten die wiederum verschiedene Namen, Geschmäcker, Größen und Farben haben.

              Gruß
              Günni
              Vermutlich ist das nicht die beste Idee, Datenbanken sind keine Exceltabellen!
              Dein obiges Beispiel ist sehr spezifisch. Brauchen Erdbeeren unbedingt eine Farbangabe?
              Um es freier zu gestalten sollte man dann die Tabelle in der Richtung aufbauen:
              product_characteristics:
              id | product_id | key | value
              1 | ? | color | green
              2 | ? | smell | sauer

              Aber grundsätzlich sind Datenbanken immer von ihrem Zweck abhängig, es gibt sicher einige "No-Go's" und einige "Best Practice" Varianten, aber in den meisten Fällen gibs mehr als eine gute Lösung

              Kommentar


              • #8
                Und dann verkauft der Obstladen auf einmal Handyhüllen mit seinem Logo drauf. Oder sieht dir die Beispieldaten an, was ist 'american' und was hätten die anderen Produkte in dem Feld für einen Wert?
                Wenn du zu deinen Entitäten variable Eigenschaften hast (und nur dann), kannst du die auch variabel verwalten. Sonst erweiterst du ständig dein Schema und musst damit dann auch deinen Code ändern.

                Dann sucht man nach Apple, und bekommt dann entweder Apple->Green, oder Apple->big . . . usw.?
                Nö, wenn nach Apple gesucht wird, lieferst du Apple und die zugehörigen Eigenschaften. Ob die Eigenschaften in einer Liste stehen oder du die beim selecten pivotisierst ist recht egal.
                Wenn gefordert kannst du natürlich auch auf die gesuchte Eigenschaft eingrenzen.

                Wenn du nach einer Eigenschaft suchst das gleiche. Da verhält es sich sogar genauso wie bei deinem vorgeschlagenen Modell, du handhabst die Eigenschaften nur anders.


                Für Preise oder andere Werte die sich wohl wirklich die meisten Produkte teilen würde ich dieses Modell aber nicht mehr einsetzen. Da ist dann die herkömmliche Struktur angesagt.
                Das Stichwort für so ein Modell ist übrigens EntityAttributeValue-Modell. Es ist nicht schwierig sich da die Nachteile zu ergooglen
                [COLOR=#A9A9A9]Relax, you're doing fine.[/COLOR]
                [URL="http://php.net/"]RTFM[/URL] | [URL="http://php-de.github.io/"]php.de Wissenssammlung[/URL] | [URL="http://use-the-index-luke.com/de"]Datenbankindizes[/URL] | [URL="https://www.php.de/forum/webentwicklung/datenbanken/111631-bild-aus-datenbank-auslesen?p=1209079#post1209079"]Dateien in der DB?[/URL]

                Kommentar


                • #9
                  Zitat von Greehorn17 Beitrag anzeigen
                  ich habe eine normalisierte Datenbank ...

                  Übergeordnete Haupttabelle (product_main_table):

                  product_id product_name
                  23 Apple
                  24 Orange
                  25 Strawberry

                  Untergeordnete Tabelle (product_characteristics):

                  characteristic_id product_name characteristic
                  23 Apple green
                  23 Apple sweet
                  23 Apple small
                  23 Apple american
                  24 Orange orange
                  24 Orange sourly
                  24 Orange big
                  24 Orange african
                  25 Strawberry red
                  25 Strawberry sweet
                  Nicht genug normalisiert... Warum "product_name" in "product_characteristics"? Das ist redundant, die Information liegt schon in "product_main_table" vor und wird korrekt per FK referenziert.
                  Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

                  Kommentar


                  • #10
                    Danke rkr für deine Vorschläge! Habe eine kurze Frage zu den Versionen 1 und 3: woher kommst das t bzw. t.id? Da bekomme ich in SQL immer eine Fehlermeldung, weil er das nicht kennt.

                    Kommentar


                    • #11
                      Das ist der Alias des jeweiligen Sub-Queries.

                      Kommentar


                      • #12
                        Ja das ist mir schon klar, aber wenn ich das so in SQL eingebe, kommt die Fehlermeldung: #1054 - Unknown column 't.id' in 'field list'

                        Kommentar


                        • #13
                          1. Du musst den Alias t auch vergeben haben.
                          2. Die Spalte id muss unter dem Alias existieren.

                          Kommentar


                          • #14
                            Habe folgende query und da bekomme ich die Fehlermeldung:

                            SELECT
                            t
                            .id
                            ,
                            p.
                            name
                            FROM
                            (
                            SELECT
                            p
                            .id
                            ,
                            COUNT(pc.product_id) AS
                            exclude_count
                            FROM
                            products p
                            LEFT JOIN
                            product_characteristics pc ON p
                            .id = pc.product_id AND pc.characteristic IN ('red', 'sweet'
                            )
                            GROUP BY
                            p
                            .id)
                            t
                            INNER JOIN
                            products p ON p
                            .id = t.
                            id
                            WHERE
                            t
                            .exclude_count = 0



                            Da wird der Alias ja am Ende der FROM-Clause vergeben. Woher jedoch kommt die id von t.id bei SELECT und INNER JOIN?

                            Kommentar


                            • #15
                              Wenn man den SQL-Code lesbar formatiert, sieht mans auch:
                              Code:
                              SELECT t.id, p.name
                              FROM (
                                  SELECT p.id, COUNT(pc.product_id) AS exclude_count
                                  FROM products p
                                  LEFT JOIN product_characteristics pc ON (
                                      p.id = pc.product_id AND pc.characteristic IN ('red', 'sweet')
                                  )
                                  GROUP BY p.id
                              ) t
                              INNER JOIN products p ON p.id = t.id
                              WHERE t.exclude_count = 0
                              Allerdings finde ich die doppelte Verwendung vom Alias "p" etwas unübersichtlich. Ich würde unterschiedliche Aliase vergeben, damit es nicht zur Verwirrung kommt.

                              Kommentar

                              Lädt...
                              X