[MSSQL] selecteren records a.d.h.v. optionele criteria

[Pelle]

Ik ben bezig met een 'wizard' om producten te selecteren aan de hand van bepaalde criteria. Elk product heeft bepaalde features, en op die features wil ik een product selecteren. In dit topic pak ik als voorbeeld een TFT monitor, waarvan ik wil dat de schermgrootte minimaal 17'' is en/of de resolutie 1280 x 1024.

Het relevante stuk datamodel ziet er zo uit:


^{(klik voor grotere versie}

In mijn geval ben ik op zoek naar een product uit de product-categorie TFT monitors. Er is nog een product-categorie-tabel, maar die doet hier niet terzake en is niet opgenomen in het schema; ik heb genoeg aan de ID van betreffende categorie, en dat is in dit geval 222.
Elke feature (resolutie, schermgrootte etc) wordt gebruikt in één of meerdere product-categorieen (resolutie is namelijk ook relevant voor bijv. notebooks en PDA's).
De feature_id van resolutie is 80 en van schermgrootte is 15.

Aan een specifiek product (table products) hangen allerlei feature-values (table product_feature) en deze feature-values zijn middels de category_feature tabel weer onder te brengen onder de naam van betreffende feature.
Oftewel, stel ik heb een harddisk (table products), dan hangen hier feature-values aan als '7200rpm' en 'SATA' (table product_feature), en deze zijn dus via table category_feature te koppelen aan de namen van deze features, in dit geval snelheid en interface.
Ik weet niet of ik het zelf zo had opgezet, maar veel kan ik er niet aan veranderen

In ieder geval, ik wil nu dus middels een formulier alle TFT monitors selecteren die een schermgrootte van minimaal 17'' hebben. Dat doe ik met de volgende query:

code:

SELECT      products.idProduct,
            products.description,
            products.price
FROM        products
INNER JOIN  product_feature
ON          product_feature.product_id          = products.idProduct
INNER JOIN  category_feature
ON          product_feature.category_feature_id = category_feature.category_feature_id
INNER JOIN  feature
ON          category_feature.feature_id         = feature.feature_id
WHERE       (feature.feature_id                 = 15)  AND
            (category_feature.catid             = 222) AND
            (product_feature.[value]            = '17 inch')

Hetzelfde kan ik doen met de resolutie:

code:

SELECT      products.idProduct,
            products.description,
            products.price
FROM        products
INNER JOIN  product_feature
ON          product_feature.product_id          = products.idProduct
INNER JOIN  category_feature
ON          product_feature.category_feature_id = category_feature.category_feature_id
INNER JOIN  feature
ON          category_feature.feature_id         = feature.feature_id
WHERE       (feature.feature_id                 = 80)  AND
            (category_feature.catid             = 222) AND
            (product_feature.[value]            = '1280 x 1024')

De bedoeling is dat als iemand straks die twee criteria heeft ingevuld, deze in een SP geramd worden die vervolgens alle TFT monitors teruggeeft die aan deze twee criteria voldoen. Ik heb dit opgelost door in de SP twee temptables aan te maken (eentje voor elk criterium) en vervolgens een join te doen op deze temptables (op idProduct) zodat ik alle TFT's heb die zowel een 17'' grootte hebben als een resolutie van 1280 x 1024.

Tot dusver geen probleem. Echter, het moet ook mogelijk zijn om alle producten te selecteren die aan één van beide criteria voldoen. Op zich is ook dát geen probleem, want dan stop je alles gewoon in één temptable en daar doe je vervolgens een SELECT DISTINCT op.
Het grootste probleem is dat ik naast TFT monitors (waarbij je 2 maar selectie-criteria hebt) ook bijvoorbeeld workstations en notebooks wil kunnen selecteren. En daar praat je over misschien wel 10 criteria, en dat gaat dus qua temptables een beetje uit de klauwen lopen.

En dus? Tsja, ik zoek dus een nette manier om dit probleem te tackelen. Oftewel, hoe bouw ik een SP die een result teruggeeft met producten die aan of alle, of één criteria voldoen, en dan het liefst nog ongeacht het aantal criteria

[Skinny]

Als je zowel de 'of' and de 'en' zoekmethode wilt doen kan je wellicht gebruiken maken van de 'full-text' mogelijkheden van je database en dan alleen op de 'value' kolom in je product_feature tabel.

code:

SELECT * FROM CONTAINSTABLE(product_feature, [value], 'search expression')

de CONTAINSTABLE levert dus een table op die je vervolgens weer kan joinen met de rest van je tabellen om na te gaan welke records uit de CONTAINSTABLE functie echt relevant zijn voor je search.

HTH

[ Voor 30% gewijzigd door Skinny op 12-11-2004 16:41 . Reden: Het gaat hier dus al om MSSQL ;) ]

[Verwijderd]

Temptable? Daar hebben ze toch een View voor uitgevonden?

[Haploid]

Wordt dat niet gewoon iets in de trant van:

code:

SELECT      products.idProduct,
            products.description,
            products.price
FROM        products
INNER JOIN  product_feature
ON          product_feature.product_id          = products.idProduct
INNER JOIN  category_feature
ON          product_feature.category_feature_id = category_feature.category_feature_id
INNER JOIN  feature
ON          category_feature.feature_id         = feature.feature_id
WHERE       ( (feature.feature_id                 = 15)  AND
            (category_feature.catid             = 222) AND
            (product_feature.[value]            = '17 inch') )
OR          ( (feature.feature_id                 = 80)  AND
            (category_feature.catid             = 222) AND
            (product_feature.[value]            = '1280 x 1024') )

Gevolgd door nog meer ORretjes als er nog meer criteria zijn. Heb je ook geen DISTINCT meer nodig.

[Pelle]

Sorry voor mijn late reactie; hele weekend niet online geweest en gister geen tijd gehad

Skinny schreef op vrijdag 12 november 2004 @ 16:40:
Als je zowel de 'of' and de 'en' zoekmethode wilt doen kan je wellicht gebruiken maken van de 'full-text' mogelijkheden van je database en dan alleen op de 'value' kolom in je product_feature tabel.

Daar zal ik eens naar kijken

Verwijderd schreef op vrijdag 12 november 2004 @ 20:03:
Temptable? Daar hebben ze toch een View voor uitgevonden?

Die mag je even uitleggen in dit verband. Ik zie niet in hoe een view me in dit geval zou kunnen helpen

Haploid schreef op vrijdag 12 november 2004 @ 20:16:
Wordt dat niet gewoon iets in de trant van:

code:

SELECT      products.idProduct,
            products.description,
            products.price
FROM        products
INNER JOIN  product_feature
ON          product_feature.product_id          = products.idProduct
INNER JOIN  category_feature
ON          product_feature.category_feature_id = category_feature.category_feature_id
INNER JOIN  feature
ON          category_feature.feature_id         = feature.feature_id
WHERE       ( (feature.feature_id                 = 15)  AND
            (category_feature.catid             = 222) AND
            (product_feature.[value]            = '17 inch') )
OR          ( (feature.feature_id                 = 80)  AND
            (category_feature.catid             = 222) AND
            (product_feature.[value]            = '1280 x 1024') )

Gevolgd door nog meer ORretjes als er nog meer criteria zijn. Heb je ook geen DISTINCT meer nodig.

Ja, daar heb ik ook aan gedacht. Echter, ik wil geen query's bouwen maar gewoon een nette SP gebruiken. Het ene hoeft het andere niet uit te sluiten, maar het vervelende van het dynamisch bouwen van queries is dat je execution plan dus nooit vast ligt en dat wil ik dus voorkomen

[P_de_B]

Pelle schreef op dinsdag 16 november 2004 @ 10:26:

Ja, daar heb ik ook aan gedacht. Echter, ik wil geen query's bouwen maar gewoon een nette SP gebruiken. Het ene hoeft het andere niet uit te sluiten, maar het vervelende van het dynamisch bouwen van queries is dat je execution plan dus nooit vast ligt en dat wil ik dus voorkomen

Ik heb er ook een tijdje over na zitten denken, volgens mij is er niet echt een nette oplossing mogelijk in een SP. Omdat de SP -als je het al netjes voor elkaar krijgt- in mijn ogen elke keer een dermate afwijkende signature heeft zal ook bij de sp het exectionplan niet gecached worden. Ik zou denk ik toch overwegen in de client applicatie een query op te bouwen.

Overigens heeft het wel of niet gebruiken van een sp geen invloed op het wel of niet chachen van het execution plan.

[Haploid]

Tja, als je het met stored procedures wil doen, dan kun je eigenlijk alleen maar in de client code nog daarop filteren. Dus dan kun je daar het beste de dubbelen eruit halen. Een oplossing met temporary tables is natuurlijk niet handig. Maar ik ben met P eens dat het niet de netste oplossing is.

Oh, en over het dynamisch bouwen van een execution plan hoef je je niet zo'n zorgen te maken. Dat zal weinig tot geen performance impact hebben op het uitvoeren van de query, tenzij we het hier over hele kleine tabellen hebben.

[ Voor 29% gewijzigd door Haploid op 16-11-2004 12:53 ]

[Pelle]

* ros *

Ik bouw nu dynamisch mijn query en dat gaat op zich prima, als ik OR gebruik om records te selecteren. Oftewel, ik staar me blind op een query die producten kan selecteren die aan 2 of meer eisen voldoen, en niet aan 1 van de 2.

Als ik in een query als in [rml]Haploid in "[ MSSQL] selecteren records a.d.h.v. opti..."[/rml] de OR vervang door AND dan gaat het fout, aangezien feature.feature_id dan zowel 15 én 80 moet zijn, en dat kan natuurlijk niet. Het is vast en zeker heel simpel, maar ik zie het even niet hoe ik 2 criteria zou kunnen combineren.

[Haploid]

Pelle schreef op dinsdag 23 november 2004 @ 12:40:
* ros *

Ik bouw nu dynamisch mijn query en dat gaat op zich prima, als ik OR gebruik om records te selecteren. Oftewel, ik staar me blind op een query die producten kan selecteren die aan 2 of meer eisen voldoen, en niet aan 1 van de 2.

Als ik in een query als in [rml]Haploid in "[ MSSQL] selecteren records a.d.h.v. opti..."[/rml] de OR vervang door AND dan gaat het fout, aangezien feature.feature_id dan zowel 15 én 80 moet zijn, en dat kan natuurlijk niet. Het is vast en zeker heel simpel, maar ik zie het even niet hoe ik 2 criteria zou kunnen combineren.

Probeer eens iets met sub-queries, in de trant van:

code:

SELECT      products.idProduct,
            products.description,
            products.price
FROM        products

WHERE EXISTS(
      SELECT 1 
      FROM        product_feature
      INNER JOIN  category_feature
      ON          product_feature.category_feature_id = category_feature.category_feature_id
      INNER JOIN  feature
      ON          category_feature.feature_id         = feature.feature_id
      WHERE       product_feature.product_id          = products.idProduct
      AND         (feature.feature_id                 = 15)  AND
                  (category_feature.catid             = 222) AND
                  (product_feature.[value]            = '17 inch')
      )

AND EXISTS(
      SELECT 1 
      FROM        product_feature
      INNER JOIN  category_feature
      ON          product_feature.category_feature_id = category_feature.category_feature_id
      INNER JOIN  feature
      ON          category_feature.feature_id         = feature.feature_id
      WHERE       product_feature.product_id          = products.idProduct
      AND         (feature.feature_id                 = 80)  AND
                  (category_feature.catid             = 222) AND
                  (product_feature.[value]            = '1280 x 1024') )

Een behoorlijke lap code, maar je hoeft 't gelukkig niet met de hand te tikken. Ik hoop dat ik het allemaal goed heb getikt Wat ie doet is kijken voor elk product of er een record is met die productid en de eerste gewenste feature, en vervolgens hetzelfde met de tweede gewenste feature.

Ik neem aan dat de code leesbaar genoeg is. Het enige nieuwe is het keyword EXISTS en het moge duidelijk zijn wat dat doet.