[MSSQL] And/or query op 1 tabel met hulp parent/child tabel

[Polderdijk]

Wat is het doel
We willen voor een webwinkel uitgebreide filtermogelijkheden aanbieden zodat je per 'hoofdfiltergroep' 1 of meerdere selecties kan maken, en als eindresultaat moeten er dan alleen de producten worden getoond die in alle geselecteerde hoofdfiltergroepen voorkomt, en dan per geselecteerde 'subfilters', minimaal 1 van de selecties.

Wat heb je op dit moment
Eén tabel met daarin producten, waarbij ProductId de primaire key is.

Daarnaast heb ik 1 tabel met daarin Productfilters, in een één-level diepe Parent/Child constructie, het tabel bevat 3 kolommen: FilterId, FilterParentId en FilterNaam, als voorbeeld:

FilterId	FilterParentId	FilterNaam
1	NULL	Opslagruimte
2	1	1Gb
3	1	4Gb
4	NULL	Kleur
5	3	Wit
6	3	Zwart

Daarnaast heb ik een tabel ProductFilter, met daarin de specificatie per product aan welke childfilter deze gekenmerkt kan worden (er wordt dus altijd een FilterId gegeven uit bovenstaande tabel waarin FilterParentId NOT IS NULL. Het is mogelijk dat 1 product meerdere FilterId's mogen hebben die de zelfde parent hebben. In dit voorbeeld kan 1 product dus zowel Kleur: Wit als Kleur: Zwart als kenmerk hebben.

FilterId	ProductId
2	100
5	100
6	100
2	101
3	101
5	101

A.d.h.v. het tabel Productfilters heb ik netjes een checkbox lijstje waaruit je kan filteren. Hierbij zijn de records met NULL als parent dus de 'kolomkoppen' van de onderliggende childs, deze kan dus NIET geselecteerd worden.

Nu is het de bedoeling dat als er geselecteerd wordt: Geheugen: 1Gb - Kleur: Zwart & Wit, dat er een query gemaakt moet worden waaruit in het tabel alle ProductId's worden gehaald waarbij de FilterId = 1Gb (alle producten die dit niet hebben, vervallen), en alles wat daaruit nog overblijft, moet ik weer de ProductId's hebben die ook alleen voorkomen in FilterId = Zwart OR FilterId = Wit.

Wat heb ik zelf al geprobeerd
Uiteraard ben ik hier al behoorlijk mee aan het stoeien, zowel in me hoofd als programmeertechnisch. Echter denk ik dat ik nu even niet de juiste denkvorm heb.

Allereerst heb ik geprobeerd op basis van meerdere subquery's met UNION aan elkaar te reigen, echter is hierbij het probleem dat je een OF/OF query krijgt. Zover ik weet bestaat er geen UNION methode waarbij je uit alle subquery's alleen de Id's krijgt die in ALLE subquery's voorkomen.
Ik heb ook geprobeerd om diversie query's uit te voeren en dan via de code in een array alleen de ProductIds te vinden die in alle subquery's voorkomen, dit is mogelijk maar kost heel veel extra databasebevragingen en denk niet dat dit de juiste manier is.

Wat is nou de beste 'logica' om hiermee om te gaan?

[NMe]

Waarom kijk je naar unions en niet bijvoorbeeld naar een subquery of een aantal subquery's i.c.m. exists?

[Polderdijk]

Daar zit 'm nou net mijn probleem in, ik kan inderdaad diverse query's opbouwen zodat ik per 'hoofdcategorie' alle ProductId's krijgt, maar hoe verwerk ik dan in die zelfde query dat ik alleen de ProductId's terug krijg, welke in ALLE subquery's aanwezig zijn?

Het mooiste zou iets in de trant zijn:

SQL:

SELECT [UNIEK] ProductId FROM (
    SELECT ProductId FROM xxxxxx,
    SELECT ProductId FROM xxxxxx,
    etc.
)

Echter zover mijn kennis gaat, is zo'n soort query niet mogelijk.

[Douweegbertje]

Even gewoon hardop denkend;

Je hebt een tabel met filters, en uiteindelijk wil je ook specifiek op bepaalde filters zoeken en hier de product ID's van hebben.

Dus als je dan iets doet in de trend van;

SELECT productid FROM filters as f WHERE filternaam = blaat OR filternaam = foo
LEFT JOIN producten as p where p.productid = f.productid

Nu, mocht je alle producten ook in een koppel tabel hebben, moet je domweg nog één extra join ervoor doen.

[NMe]

Polderdijk schreef op maandag 10 februari 2014 @ 22:02:
Daar zit 'm nou net mijn probleem in, ik kan inderdaad diverse query's opbouwen zodat ik per 'hoofdcategorie' alle ProductId's krijgt, maar hoe verwerk ik dan in die zelfde query dat ik alleen de ProductId's terug krijg, welke in ALLE subquery's aanwezig zijn?

AND?

SQL:

SELECT    *
FROM      producttabel p
WHERE     EXISTS (SELECT ... FROM filterkoppeltabel WHERE filterid = 1 AND productid = p.productid)
  AND     EXISTS (SELECT ... FROM filterkoppeltabel WHERE filterid = 2 AND productid = p.productid)
  AND     ...

Het mooiste zou iets in de trant zijn:

SQL:

SELECT [UNIEK] ProductId FROM (
    SELECT ProductId FROM xxxxxx,
    SELECT ProductId FROM xxxxxx,
    etc.
)

Echter zover mijn kennis gaat, is zo'n soort query niet mogelijk.

Zo'n query is wel mogelijk maar die lost je probleem niet op.

[Polderdijk]

Na nog even flink door te zoeken eigenlijk een super simpele oplossing gevonden.

In de software kant bouw ik diverse subquery's op in de vorm van SELECT ProductId FROM Tabel WHERE FilterId IN (1, 2), en dat per 'hoofdfilter'.

Daarna execute ik deze met een briljante functie INTERSECT en klaar, heb ik alleen de ProductId's die in alle tabellen voorkomen!

[NMe]

Het zijn geen subquery's als je union/intersect/except gebruikt om ze te combineren. Dat zijn gewoon losse query's waarvan je toevallig het resultaat aan elkaar plakt. Ik zou als ik jou was wel even jouw execution plan naast dat van mij leggen en kijken welke beter performt, want dat weet ik zo niet.

[Douweegbertje]

Oh trouwens, doe gewoon eens dit proberen?

code:

SELECT productid FROM filters as f WHERE filternaam = blaat OR filternaam = foo
INNER JOIN ProductFilter as pf where pf.productid = f.productid
LEFT JOIN producten as p where p.productid = pf.productid

Zoiets moet volgens mij gewoon werken. Met joins (de juiste) kun je vrij wel alles doen. En voor je eerste join moet je denk ik een INNER hebben om beide te combineren, en vervolgens left join je de product informatie over de overgebleven zooi.

In elk geval allerlei subquery's zou niet nodig moeten zijn. Daarbij zou ik eens een goed opletten of je wel alle data fatsoenlijk pakt. Dat iets "werkt" wilt nog niet zeggen dat je alle data goed te pakken hebt.

[NMe]

Daarmee heb je slechts één filter gecheckt, tenzij je alsnog lomp gaat subqueryen, joinen of intersecten. In tegenstelling tot je vorige suggestie, waarin je alles met één of meer gematchte filters had. Beiden zijn niet wat de TS vraagt.

[Polderdijk]

Ik krijg 'm nog niet helemaal af, morgen zal ik inderdaad beide oplossingen eens bouwen en vergelijken met het execution plan.

Zodra de resultaten er zijn, post ik ze meteen, interessant vergelijkmateriaal!

[Douweegbertje]

NMe schreef op maandag 10 februari 2014 @ 22:26:
Daarmee heb je slechts één filter gecheckt, tenzij je alsnog lomp gaat subqueryen, joinen of intersecten. In tegenstelling tot je vorige suggestie, waarin je alles met één of meer gematchte filters had. Beiden zijn niet wat de TS vraagt.

Nou nu snap ik dan wellicht heel de vraag niet, maar wat is hier verkeerd aan? Zo heb ik gewoon een selectie op meerdere filters.

http://www.sqlfiddle.com/#!2/63d6a/1

[Hydra]

Heb je al eens gedacht aan een lucene gebaseerde oplossing? Faceted search is typisch iets wat niet super goed schaalt op een RDBMs.

http://www.elasticsearch.org/

[NMe]

Douweegbertje schreef op maandag 10 februari 2014 @ 22:57:
[...]

Nou nu snap ik dan wellicht heel de vraag niet, maar wat is hier verkeerd aan? Zo heb ik gewoon een selectie op meerdere filters.

http://www.sqlfiddle.com/#!2/63d6a/1

Hmm, ik denk dat ik je query niet helemaal doorgrondde. Overigens heb je dan wel meerdere rijen per product, maar dat zou je kunnen oplossen door distinct te selecteren uit het gehele resultaat van deze query.

[Polderdijk]

@Douweegbertje: denk dat je idd nog niet helemaal mijn princiepe begrijpt, je voorbeeld (waarvan overigens dank!) klopt niet zoals het moet.
Je gebruikt hier puur een OR vergelijking, welke niet klopt: K.FilterNaam = '1Gb' OR K.FilterNaam = '4Gb' OR K.FilterNaam = 'Wit'.

In het door jou gegeven voorbeeld heb ik geen 'hoofdcategorieën' (geheugen of kleur). Het is namelijk de bedoeling dat 'per hoofdcategorie' een OR vergelijking moet plaatsvinden, en van die uitkomst wil ik alleen artikelen hebben die in ALLE 'hoofdcategorieen' uit de filter kwamen, als er een productid in één van de OR-vergelijkingen dus niet voorkom, wil ik deze niet zien.

Maar om even terug te komen op het voorbeeld van NMe (EXISTS) vs INTERSECT in de query analyzer bekeken, en wonderwel (na het oplossen van missende index) maakt beide oplossingen totaal geen verschil.
Omdat de oplossing met INTERSECT makkelijker te programmeren was, heb ik dus uiteindelijk hiervoor gekozen.

Helaas vind ik het nog wel jammer dat ik hiervoor toch nog eerst een extra query moet draaien om de parents te krijgen (om daaruit weer de juiste INTERSECT op te bouwen). Ik ga hier nog eens naar kijken of ik die via JSON of iets alvast gegroepeerd aangeleverd kan krijgen, want beide 'mechanismen' in 1 query proppen lijkt me zeer onmogelijk.

[NMe]

Je parents hebben toch ook gewoon een tabel in de database waar je op kan joinen?

[Douweegbertje]

Nogmaals, misschien snap ik het nog steeds niet maar toch even er mee bemoeien. Naar mijn mening doe je echt veel te moeilijk. In feite zeg je: Ik wil dat een product op zijn minst aan één voorwaarde voldoet: de hoofdcategory. Vervolgens als dat 'true' is, moet er gekeken worden of hij dan op zijn minst één van de 'child' cats aanwezig is.

Dus in kort, je zoekt eerst op "kleur" en pakt vervolgens alles wat hier dan Wit/Zwart is.

In elk geval heb ik mij even vermaakt, maar dan moet dit het dan toch zijn;

http://www.sqlfiddle.com/#!2/0025d/3

SQL:

SELECT *,kk.filterID as subFilterID, kk.FilterNaam as subFilter FROM koppel as k 
LEFT JOIN koppel as kk on kk.FilterParentID = k.filterID
LEFT JOIN Productfilters as pf on pf.filterID = kk.filterID
LEFT JOIN producten as p on p.productID = pf.productID
WHERE k.FilterNaam = 'Kleur' and k.FilterParentID IS NULL
AND kk.FilterNaam = 'Zwart' OR kk.FilterNaam = 'Wit'
 

In feite haal ik eerst de "parent" op, en join er weer de zelfde tabel op terug, waar het filterID gelijk is met het parentID. Dan heb je -alles- qua parent/child.

oh en voordat de vraag weer komt;
Je kunt dit domweg uitbreiden met een OR, waar je de WHERE clause specifiek per "zoekopdracht" tussen de () zet.

http://www.sqlfiddle.com/#!2/2e393/1

SQL:

SELECT *,kk.filterID as subFilterID, kk.FilterNaam as subFilter FROM koppel as k 
LEFT JOIN koppel as kk on kk.FilterParentID = k.filterID
LEFT JOIN Productfilters as pf on pf.filterID = kk.filterID
LEFT JOIN producten as p on p.productID = pf.productID
WHERE 
(k.FilterNaam = 'Kleur' and k.FilterParentID IS NULL
AND kk.FilterNaam = 'Zwart' OR kk.FilterNaam = 'Wit')
OR
(k.FilterNaam = 'Opslagruimte' and k.FilterParentID IS NULL
AND kk.FilterNaam = '1Gb' OR kk.FilterNaam = '4Gb')
 

Zo krijg je gewoon een fatsoenlijk result en kun je in de programmateur makkelijk zien in welke cat elk product zit. Mocht je alleen de productnamen willen hebben (en niet willen weten of hij nou alleen in zwart is of beide) kun je simpel een

SQL:

GROUP BY productNaam
 

Erbij gooien op het einde.

[ Voor 38% gewijzigd door Douweegbertje op 11-02-2014 23:14 ]

[NMe]

Douweegbertje schreef op dinsdag 11 februari 2014 @ 23:05:
Nogmaals, misschien snap ik het nog steeds niet maar toch even er mee bemoeien. Naar mijn mening doe je echt veel te moeilijk. In feite zeg je: Ik wil dat een product op zijn minst aan één voorwaarde voldoet: de hoofdcategory. Vervolgens als dat 'true' is, moet er gekeken worden of hij dan op zijn minst één van de 'child' cats aanwezig is.

Nu je het zegt, en na even nalezen van de TS: dat staat er inderdaad. En dat zou jouw query gewoon af moeten handelen.

Die query van mij en die query van de TS zelf vereisen dat alle subfilters óók allemaal matchen. Dat komt niet overeen met de vraag in de topicstart. Wat is nu precies de bedoeling?

[Douweegbertje]

@ts.. heb je nog iets hiermee gekund?

[Sircuri]

Ben benieuwd inderdaad wat de TS hiermee doet.
Het komt namelijk op mij over als dat er een data model is verzonnen voor een probleem die nog niet begrepen wordt.

Ik snap niet waarom er niet gezocht wordt d.m.v.:

SELECT * FROM ProductFilter WHERE FilterId IN ( <gekozen filters> )

[NMe]

Het enige ongelukkige is dat hij filters en mogelijke waardes voor die filters in dezelfde tabel gepropt heeft. Met de gegeven voorbeelden is dat in elk geval niet logisch.