Ankündigung

Einklappen
Keine Ankündigung bisher.

Benötige Hilfe bei komplexer SQL Abfrage für Filter

Einklappen

Neue Werbung 2019

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

  • Benötige Hilfe bei komplexer SQL Abfrage für Filter

    Hallo,

    ich habe ein Formular, über das ich zuvor zugeordnete Einträge aus der Datenbank gefiltert ausgeben möchte.

    Das Formular hat folgenden Aufbau.
    ---------------------------
    1. Frau oder Mann?
    ---------------------------
    2. Alter wie z.b. 10, 20, 30, 40, 50
    ---------------------------
    3. Viele verschiedene Vorlieben wie Essen, Trinken, Feiern, Reisen, Sport
    ---------------------------
    4. Beziehung: Freunde, Familie, Bekannte
    ---------------------------

    Das Formular besteht aus checkboxen, von denen ich beliebig viele Auswählen kann und in beliebiger Reihenfolge.

    Ich habe in der Datenbank Produkte hinterlegt, die in einer Tabelle den jeweiligen Kategorien zugeordnet sind.

    Ein Produkt ist z.B. nur für Männer, 20 Jahre, Hat etwas mit Sport und Reisen zu tun und nur für Freunde.

    Wenn ich aber jetzt z.B. "Männer" und 10 jahre wähle, bekomme ich das gleiche Produkt angezeigt, da es auch für die Kategorie 1 "Männer" zugeordnet wurde obwohl es vom alter nicht mehr passt.

    Wie löse ich das Problem, das die einzelnen Gruppen und die abhängigkeit der Kategorien berücksichtigt werden?

    Mein Ansatz ist so, ich bekomme aber immer 0 zeilen Raus obwohl es ein Produkt gibt das passen muss.

    PHP-Code:
    SELECT products.* FROM products 
    JOIN product_in_kategorie ON products
    .id product_in_kategorie.product_id
    JOIN kategorien ON product_in_kategorie
    .kategorie_id kategorien.id
    WHERE 
    (product_in_kategorie.kategorie_id IN (152334) AND kategorien.gruppe 1) AND 
    (
    product_in_kategorie.kategorie_id IN (152334)  AND kategorien.gruppe 2
     AND 
    (
    product_in_kategorie.kategorie_id IN (152334)  AND kategorien.gruppe 3)
     AND 
    (
    product_in_kategorie.kategorie_id IN (152334)  AND kategorien.gruppe 4)  LIMIT 20 
    Also ich will das die Abfrag folgendes kann z.B.

    Ich suche was für Frauen, im alter von 20 oder 30 Jahre, mit den Interessen Essen oder Feiern oder Reisen und Die Beziehung ist Freunschaft oder Familie.
    Dann sollen nur die Produkte ausgegeben werden, die genau zu diesen Kriterien passen und absolut nichts was nur für Männer zugeordnet wurde aber von alter her passt.

    Meine Überlegung war, das ganze in mehrere Abfragen zu teilen, so dass ich erst abfrage, ist es was für Männer oder Frauen oder ist es egal.. Danach den Rest usw..
    Das Problem bei dieser Vorgehensweise ist, das ich mit LIMIT 20 arbeite und eventuell gar nicht bis zu 3. oder 4. Abfrage komme, obwohl es noch genug ungeprüfte Einträge gäbe die wegen dem LIMIT nicht berücksichigt werden.
    Deswegen müsste alles in einer Abfrage gemacht werden.

  • #2
    Zitat von eclipse240hp Beitrag anzeigen

    Mein Ansatz ist so, ich bekomme aber immer 0 zeilen Raus obwohl es ein Produkt gibt das passen muss.

    Gemäß Deiner Abfrage logisch. Da 1 != 2 != 3 != 4 gilt, nicht aber 1 = 2 = 3 = 4.

    Was für eine DB? Für PostgreSQL könntest Du mit Array's arbeiten, via Contrib-Modul INTARRAY auch mit passender Indexunterstützung.

    Ansonsten halt das übliche : normalisieren und joinen. Wirst das schon schaffen ...
    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Kommentar


    • #3
      PHP-Code:
      SELECT products.* FROM products 
      JOIN product_in_kategorie ON products
      .id product_in_kategorie.product_id
      JOIN kategorien ON product_in_kategorie
      .kategorie_id kategorien.id
      WHERE 
      (product_in_kategorie.kategorie_id IN (152334) AND kategorien.gruppe 1) AND 
      (
      product_in_kategorie.kategorie_id IN (152334)  AND kategorien.gruppe 2
       AND 
      (
      product_in_kategorie.kategorie_id IN (152334)  AND kategorien.gruppe 3)
       AND 
      (
      product_in_kategorie.kategorie_id IN (152334)  AND kategorien.gruppe 4)  LIMIT 20 
      Ich habe jetzt nicht gelesen, was du genau möchtest, allerdings sollte das dort so aussehen:
      PHP-Code:
      SELECT products.* FROM products 
      JOIN product_in_kategorie ON products
      .id product_in_kategorie.product_id
      JOIN kategorien ON product_in_kategorie
      .kategorie_id kategorien.id
      WHERE 
      (product_in_kategorie.kategorie_id IN (152334) AND 
      (
      kategorien.gruppe OR kategorien.gruppe OR kategorien.gruppe OR kategorien.gruppe 4)  LIMIT 20 
      Zitat von derwunner
      "Ein FISI ist auf gut-deutsch der Netzwerker. Das heißt Du gehst rauß zum Kunden oder auf die Straße und verlegst Leitungen" - derwunner 2015

      Kommentar


      • #4
        Die kategorien.gruppe kannst du weglassen. Damit kannst du den Query auf das kürzen:

        PHP-Code:
        SELECT products.* FROM products 
        JOIN product_in_kategorie ON products
        .id product_in_kategorie.product_id
        WHERE 
        product_in_kategorie
        .kategorie_id IN (152334
        Da IN aber eine Oder Verknüpfung ist, bekommst du zu jeder kategorie_id die zugehöhrigen Artikel. Wenn du auf die Idee kommst daraus ein Und Verknüpfung zu machen, bekommst du gar nix zurück. Das warum hat akretschmer schon angedeutet. Ein Feld in einem Datensatz kannnicht gleichzeitig mehrere Werte darstellen (jedenfalls in dem Fall). Wenn du schreibst kategorie_id = 1 AND kategorie_id = 2, dann ist eine Aussage davon immer falsch und eine evtl. richtig. Das ist das selbe wie 1 = 2.
        Also musst du beim Oder bleiben. In der Ergebnismenge des Queries mit Oder verstecken sich nämlich auch deine gesuchten Datensätze. Du bekommst für jede kategorie_id die zugehörigen Artikel. Ist ein Artikel in 2 zutreffenden Kategorien taucht er zweimal in der Ergebnismenge auf. Ist er in 4 Kategorien taucht er viermal auf. Ist er in keiner taucht er gar nicht auf usw...
        Das heißt: taucht ein Artikel so oft auf wie Optionen ausgewählt sind, ist es einer der alle Bedingungen erfüllt. Also musst du danach Filtern:


        PHP-Code:
        SELECT products.* FROM products 
        JOIN product_in_kategorie ON products
        .id product_in_kategorie.product_id
        WHERE 
        product_in_kategorie
        .kategorie_id IN (152334GROUP BY products.id HAVING COUNT(*) = ANZAHL_DER_GEWÄHLTEN_OPTIONEN //in dem Fall also COUNT(*) = 4 

        Kommentar


        • #5
          Danke erc!
          Deine Lösung funktioniert sehr gut!

          Die Datenbank ist MySQL.

          Leider ist mir jetzt aufegfallen, das diese Lösung zu sehr filtert.

          Was ich mir wünsche ist, das bei den Kategorien nicht darauf gefiltert wird, dass ein Produkt zu allen ausgewählten Bedingungen passt, sondern jeweils eine davon schon reicht. Wenn eins zu mehreren Passt, dann soll es ggf. danach sortiert werden.

          Also wenn eine "Frau" die Vorlieben Essen und Reisen hat, sollen nicht nur die Produkte angezeigt werden die mit Essen UND Reisen zu tun haben, sondern auch die für sich nur was mit Essen oder Reisen zu tun haben.

          Ich denke der Ansatz ist hier die einzelnen Gruppen der Kategorien separat zu filtern, also erst ob es für Mann oder Frau oder beide ist, dann davon das Alter einzugrenzen, dann die Vorlieben, und dann die passende Beziehung.

          Geht so eine Idee überhaupt mit einer Abfrage, oder müssen hier mehrere hintereinander ausgeführt werden? Das wäre für die Performance sicher keine gute Idee.

          ---------------------

          Meine zweite Überlegung ist, für jedes Produkt einen Datensatz anzulegen, der mehrere Spalten für jede Kategorie enthält und dann die spalte mit ture oder false gefüllt ist. Dann brauche ich immer nur einen Datensatz prüfen und kann innerhalb der Abfrage mit AND und OR arbeiten. Performanter wäre es auch glaube ich.
          Oder gehe ich bei dieser Lösung komplett falsch ran?

          Kommentar


          • #6
            Performance ist relativ. Grade bei der Problematik ist das komplett abhängig von den Daten. Ungünstige ist bei dieser Lösung besonderes wenn du eigenschaften hast die sehr viele Artikel treffen, wie z.B. Mann/Frau. Da musst du schauen ob die Lösung überhaupt so für dich in Frage kommt.
            Ausbauen lässt sie sich auf jedenfall. Du kannst diese Lösung als Subquery verschachteln. Du suchst erst die Muss Eigenschaften und packst das als Subquery in die FROM Klausel (als Tabelle products ) und dann machst du das selbe Spiel für die Kann Eigenschaften. Dort filterst du dann aber nicht nach HAVING COUNT(*) = x sondern lässt es weg oder machst irgendwas wie COUNT(*) > x/2 um alle zu finden die mindestens die Hälfte der Kann Eigenschaften habe.

            PHP-Code:
            SELECT 
               
            ...
            FROM
             
            (SELECT products.* FROM products 
            JOIN product_in_kategorie ON products
            .id product_in_kategorie.product_id
            WHERE 
            product_in_kategorie
            .kategorie_id IN (muss eigenschaftenGROUP BY products.id HAVING COUNT(*) = anazhl muss eigenschaften) AS products JOIN product_in_kategorie ON products.id product_in_kategorie.product_id
            WHERE 
            product_in_kategorie
            .kategorie_id IN (kann eigenschaftenGROUP BY products.id 
            Das ganze lässt sich auch optimieren. Das wird dann aber recht komplex. Du müsstest dann für Eigenschaften die sehr wenige Artikel treffen ein extra join machen. (dazu müsstest du aber auch erstmal wissen welche Eigenschaften das sind usw.)

            Oder um die Ecke gedacht. Sowas lässt sich auch mit einem Volltextindex umsetzen. Du erstellst für jedes Produkt eine Text Spalte in der du sämtliche Eigenschaften schreibst. Dann kannst du mit einem Volltextindex sehr effizient nach Suchen. Z.B:
            PHP-Code:
            MATCH(textspalteAGAINST ('+Mann +Freunde +(Reise Urlaub Film)' IN BOOLEAN MODE); //Reise, Urlaub, Film sind optional, aber mindestens eins davon 
            Du kannst dabei auch die Ids nehmen. Die müssen aber dann mindestens 3 oder 4 Ziffern(?) lang sein.

            Kommentar


            • #7
              Ich probiere gleich mal die Lösung aus, bei der ich für jedes Produkt alle Spalten für die Kategorien erstelle und mit true und false fülle.

              Das habe ich schon mal ähnlich in einem anderem Projekt umgesetzt und ich glaube es ist später einfacher, falls mal eine Kategorie hinzu kommt.

              Hast du einen Tipp für mich wie ich die Ausgabe dann mit den meißten "true" sortiert ausgebe? So dass die Abfrage zählt wiviele true jeweils in einem Datensatz sind...

              Kommentar


              • #8
                Ich habe jeztz die Lösung mit den true und false umgesetzt und diese scheint sehr gut zu funktionieren.

                Jetzt würde ich gerne die gefundenen datensätze nach den mit den meisten true treffern sortieren.. wie muss ich denn hier vorhehen?? Geht das mit einer Pseudo spalte??

                Das ist meine SQL query
                PHP-Code:
                SELECT products.* FROM products
                JOIN product_in_kategorie_2 ON products
                .id product_in_kategorie_2.product_id
                WHERE  
                (  product_in_kategorie_2.c1 1  OR  product_in_kategorie_2.c2 1  )  
                AND  (  
                product_in_kategorie_2.c5 1  )  
                AND  (  
                product_in_kategorie_2.c16 1  OR  product_in_kategorie_2.c22 1  
                    
                OR  product_in_kategorie_2.c25 1  OR  product_in_kategorie_2.c23 1  
                    
                OR  product_in_kategorie_2.c41 1  )  
                AND  (  
                product_in_kategorie_2.c35 1  )  LIMIT 20 

                Kommentar


                • #9
                  Zusammenzählen...
                  PHP-Code:
                  ORDER BY product_in_kategorie_2.c1 product_in_kategorie_2.c2 product_in_kategorie_2.c5 1  +  product_in_kategorie_2.c16 ... 

                  Kommentar


                  • #10
                    Das scheint so leider nicht zu funktionieren, da ja jeder Datensatz den selben wert hat und es werden so nicht die am besten passenden Ergebnisse als erstes ausgegeben.

                    Außerdem möchte ich später auch danach sortieren, welcher Artikel die meisten Klicks hat, das überschneidet sich mit dem anderem ORDER BY.

                    Ich glaube die Lösung hierfür wäre, eine Preudo spalte, bei der je nach Treffer +1 gezahlt wird.. leider weiß ich nicht wie ich das für jeden Datensatz je nach treffer machen soll, so dass ich dann die Ergebnisse auch nach den meisten Klicks sortieren kann.

                    Es sollen dann die besten Ergebnisse mit den meisten Klicks als erstes angezeigt werden.. Ist hier ein sub Select die Lösung bezüglich den Klicks?

                    Kommentar


                    • #11
                      Zitat von eclipse240hp Beitrag anzeigen
                      Das scheint so leider nicht zu funktionieren, da ja jeder Datensatz den selben wert hat und es werden so nicht die am besten passenden Ergebnisse als erstes ausgegeben.
                      Bitte genau hinschauen. Es wird nicht der Spalteninhalt summiert, sondern Spalteninhalt = 1. Wenn Spalteninhalt = 1 -> 1 ansonsten 0.

                      Zitat von eclipse240hp Beitrag anzeigen
                      Außerdem möchte ich später auch danach sortieren, welcher Artikel die meisten Klicks hat, das überschneidet sich mit dem anderem ORDER BY.
                      Du kannst auch nach mehreren kritieren sortieren.

                      Kommentar


                      • #12
                        Ja so mache ich das ja. Hier eine Beispiel Abfrage

                        PHP-Code:
                        SELECT FROM product_in_kategorie_2
                        WHERE  
                        (  product_in_kategorie_2.c1 1  )  AND  (  product_in_kategorie_2.c21 1  OR  product_in_kategorie_2.c23 1  )  
                        ORDER BY  product_in_kategorie_2.c1 1  +  product_in_kategorie_2.c21 1  +  product_in_kategorie_2.c23 1  DESC  
                        LIMIT 0
                        20
                        die Datensätze werden zwar schon nach der Relevanz sortiert, aber z.B. trifft der erste ausgegebene Datensatz auf c1 und c21 zu.. und der zweite für alle drei.
                        Was ich möchte ist, das die Datensatze, die auf alle Kategorien passen, auch als ersten ausgegeben werden und die zu weniger passen danach kommen.

                        Geht das nicht vieleicht mit einem Subselect, wo dann die passenden Spalten gezählt werden und dann in einer pseudo Spalte ausgegeben werden, so dass man dann danach sortieren kann?

                        Kommentar


                        • #13
                          Ich habe es hinbekommen..
                          Es lag an dem = 1; in dem ORDER BY.. da war überflüssig, da die spalten ja schon 1 oder 0 sind...

                          Aber dein Absatz hat mir sehr geholfen @erc

                          Die richtige Abfrage geht so

                          PHP-Code:
                          SELECT FROM product_in_kategorie_2 
                          WHERE  
                          (  product_in_kategorie_2.c1 1  )  AND  (  product_in_kategorie_2.c21 1  OR  product_in_kategorie_2.c23 1  )   
                          ORDER BY  product_in_kategorie_2.c1  +  product_in_kategorie_2.c21   +  product_in_kategorie_2.c23 DESC   
                          LIMIT 0
                          20
                          Wie kann ich denn jetzt noch zusätzlich nach klicks sortieren, so dass die Sortierung der Relevanz erhalten bleibt und diese untereinander nach Klicks sortiert werden?

                          Wenn ich noch eine andere Spalte in das ORDER BY einbaue z.B. so

                          PHP-Code:
                          ORDER BY  product_in_kategorie_2.c1  +  product_in_kategorie_2.c21   +  product_in_kategorie_2.c23anz_klicks DESC 
                          dann sortiert er nur noch nach den klicks und schmeißt dir Relevanz wieder auseinander.

                          Kommentar

                          Lädt...
                          X