Wie filtert man nach mehreren Werten mit mehreren JOINs?

Question

Dies ist ein vager Titel, also korrigieren Sie ihn bitte, wenn Ihnen ein besserer einfällt.

Betrachten Sie diese 4 Tabellen:

Produkte : id (int), name, msrp, etc...

Teilprodukte : id (int), product_id (int), name (varchar)

subproducts_properties : id (int), subproduct_id (int), property_id (int)

subproducts_properties_values : subproducts_properties_id (int), value (varchar)

Der Grundgedanke dabei ist, dass ein einzelnes Produkt mehrere Unterprodukte (Modelle) haben kann, ein einzelnes Unterprodukt kann mehrere Eigenschaften (oder Spezifikationen) haben, und eine einzelne Eigenschaft für ein Unterprodukt kann mehrere Werte haben.

Stellen Sie sich nun vor, dass es ein Produkt gibt, das mehrere Unterprodukte hat, die mehrere Eigenschaften haben, die mehrere Werte haben. Insbesondere hat dieses Produkt ein Unterprodukt, das diese Eigenschaften hat:

Eigenschaft 1 - property_id: 1; value = '.17 HMR';

Eigenschaft 2 - property_id: 22; value = 'Bolzen';

Wo property_id 1 hat einen Namen Caliber y property_id 2 hat einen Namen Action (zum Beispiel: Waffen).

Was dieses Produkt no haben ist ein Teilprodukt, das eine Eigenschaft mit property_id=1 y value='5.56 mm NATO' .

Dem Benutzer stehen Dropdown-Felder zur Verfügung, aus denen er mehrere Filtersätze auf der Grundlage eindeutiger Werte auswählen kann. Wählt ein Benutzer also einen Caliber de .17 HMR und ein Action de Bolt sollte er erwarten, dass er unser Produkt sieht, aber wenn er sich zurückzieht Bolt und eine Caliber von, sagen wir, 5.56 mm NATO sollte er sehen keine Produkte, weil unser Produkt nicht zu beiden Filtern passt.

In Anbetracht dieser Informationen würde ich gerne alles zurückziehen. Produkte (ein Produkt pro Zeile) in der Datenbank und filtern nach mehreren Eigenschaftswerten. Mein aktueller Versuch geht so:

SELECT p.*, m.name as manufacturer_name, pt.name as product_type_name, COUNT(DISTINCT com.id) AS num_reviews, ROUND(AVG(com.rating), 1) as rating, pi.image_thumb 
FROM products p 
        LEFT JOIN manufacturers m ON p.manufacturer_id=m.id 
        LEFT JOIN product_types pt ON p.product_type_id=pt.id 
        LEFT JOIN comments com ON p.id=com.object_id AND com.object_group = 'com_products' AND com.level=0 
        LEFT JOIN ( 
                SELECT product_id, thumb_path as image_thumb 
                FROM products_images pi 
                ORDER BY ordering ASC 
            ) AS pi ON p.id=pi.product_id 
        LEFT JOIN subproducts sp ON p.id=sp.product_id 
        LEFT JOIN subproducts_properties spp ON sp.id=spp.subproduct_id 
        LEFT JOIN subproducts_properties_values sppv ON spp.id=sppv.sp_id 
WHERE p.deleted != 1 AND p.published=1 
            AND ( 
                        IF(spp.property_id=1, IF(sppv.value='5.56 mm NATO',1,0), 0) = 1 
                    OR IF(spp.property_id=22, IF(sppv.value='Bolt',1,0), 0) = 1
                ) 
GROUP BY p.id 
ORDER BY p.created DESC 
LIMIT 0, 12

Der Teil, auf den man sich konzentrieren muss, ist der letzte AND im WHERE Klausel, in der ich versuche, die Filter in Gang zu setzen. Beachten Sie auch, dass ich eine GROUP BY um Aggregatfunktionen auf anderen Tabellen durchführen zu können. Diese spezielle Abfrage wird unser Produkt zurückziehen, weil die OR in diesem letzten AND aber ich würde es gerne so einrichten, dass es in diesem Fall nicht zurückgezogen wird, sondern dass es zurückgezogen wird, wenn anstelle von sppv.value='5.56 mm NATO' Es gibt sppv.value='.17 HMR' (das ist ein Wert für unser Teilprodukt).

Ich habe versucht, stattdessen eine UND-Verknüpfung vorzunehmen, aber sie liefert nichts, weil jeder Wert eine eigene Zeile in der Tabelle sppv hat.

Bitte um Hilfe! Ich weiß nicht, was ich tun soll.

Vielen Dank im Voraus!

Answer 1

Versuchen Sie anstelle des Wo ein Haben - Zählen - Wenn:

WHERE p.deleted != 1 AND p.published=1 
GROUP BY p.id 
HAVING COUNT(IF(spp.property_id = 1 AND sppv.value='5.56 mm NATO',1,NULL)) > 0 
       AND COUNT(IF(spp.property_id=22 AND sppv.value='Bolt',1,NULL)) > 0
ORDER BY p.created DESC