SQL AND/OR zoekfilter implementeren.

[gnoe93]

Beste tweakers,

Ik ben een website aan het bouwen waarop men adhv verschillende criteria naar producten kan zoeken. Sommige van deze producten kunnen meerdere criteria tegelijk hebben, vandaar dat ik het nuttig vind een AND/OR optie te voorzien (bijvoorbeeld ik wil een stoel die zowel zwart als wit is tov ik wil zwarte of witte stoelen). Ik zal verder in mijn uitleg conceptueel een stoel als voorbeeld blijven gebruiken.

Voor dit project in kwestie gebruik ik Symfony 3 en de Doctrine 2 querybuilder. Stel ik heb 2 Entities Chair en Color die gemapt zijn aan 3 tabellen chair, chair_has_color en color.

De gebruiker vraagt in zijn zoekopdracht naar 3 kleuren stoelen:

PHP:

$colorIds = array(1, 2, 3);

Stel dat hij alle stoelen met de opgegeven kleuren wil (OR), kan dit eenvoudig met een IN statement:

PHP:

$queryBuilder
->select('chair')
->from('Chair', 'chair')
->innerJoin('chair.colors', 'color')
->where($queryBuilder->expr()->in('color.id', ':colorIds'));
->setParameter('colorIds', $colorIds);

Hoe kan ik een query schrijven zodat de gebruiker een stoel kan opvragen die deze 3 kleuren tesamen heeft (AND)?

PHP:

$queryBuilder
->select('chair')
->from('Chair', 'chair')
->innerJoin('chair.colors', 'color');

$parameters = array();

foreach ($colorIds as $i => $colorId)
{
    $queryBuilder->andWhere($queryBuilder->expr()->eq('color.id', ':colorId' . $i));
    $parameters['colorId' . $i] = $colorId;
}

$queryBuilder->setParameters($parameters);

Bovenstaande manier van werken werkt alleszins niet omdat een enkele record niet meer dan 1 ID kan hebben. Ik zou met een subquery misschien de IDs van de stoel kunnen ophalen, maar hoe kan ik deze vervolgens vergelijken met de gevraagde IDs?

[Bloemkoolsaus]

Voor elke kleur die je wilt hebt een aparte join maken van de colors tabel.

[gnoe93]

Bloemkoolsaus schreef op vrijdag 30 september 2016 @ 10:49:
Voor elke kleur die je wilt hebt een aparte join maken van de colors tabel.

Kan je misschien iets meer verklaren wat je hier mee bedoelt?

[DJMaze]

Doctrine + Symfony is leuk maar visueel ziet je code er complexer uit dan gewoon zelf eerst een query te bouwen.
Schrijf dus eerst de query volledig en test deze, bouw hem daarna om naar Doctrine als dat moet.
Ja, je werkt niet sneller maar langzamer omdat je nu 2x iets doet dat ook in 1x had gekund.

Met meerdere JOINS moet je nu ook opeens een GROUP BY gaan toepassen.

Het kan ook gewoon met sub-queries hoor.
AND:

SQL:

WHERE 3 = (SELECT COUNT(*) FROM chair_has_color WHERE chair_id = 1 AND color_id IN (1, 2, 3))

OR:

SQL:

WHERE 0 < (SELECT COUNT(*) FROM chair_has_color WHERE chair_id = 1 AND color_id IN (1, 2, 3))

AND + OR (kleur 1+2 of 3)

SQL:

WHERE 2 = (SELECT COUNT(*) FROM chair_has_color WHERE chair_id = 1 AND color_id IN (1, 2))
OR  0 < (SELECT COUNT(*) FROM chair_has_color WHERE chair_id = 1 AND color_id = 3)

In alle gevallen is het vast langzamer dan als je het opsplits in queries.
Bij 10 producten is het niet erg, maar wat bij 200 of zelfs 100.000?

[pedorus]

Volgens mij klopte het gebeuren met joins wel als efficiënte oplossing. Als in:
AND:

SQL:

select * from chair c 
join chair_has_color c1 on c.chair_id=c1.chair_id and c1.color_id=1 
join chair_has_color c2 on c.chair_id=c2.chair_id and c2.color_id=2 

OR:

SQL:

select * from chair c 
left join chair_has_color c1 on c.chair_id=c1.chair_id and c1.color_id=1 
left join chair_has_color c2 on c.chair_id=c2.chair_id and c2.color_id=2 
WHERE (not (c1.chair_id is null and c2.chair_id is null))

of met subqueries zou ik zorgen dat ze onafhankelijk zijn en niet afhankelijk zoals hierboven. Dus iets als (OR):

SQL:

select * from chair
where chair_id IN (select chair_id from chair_has_color where color_id=1)
   OR chair_id IN (select chair_id from chair_has_color where color_id=2)

Wat het handigst is, hangt van je db-engine af.

[Bloemkoolsaus]

gnoe93 schreef op vrijdag 30 september 2016 @ 20:00:
[...]

Kan je misschien iets meer verklaren wat je hier mee bedoelt?

Ik wilde expres niet teveel voor zeggen
pedorus z'n post laat precies zien wat ik in gedachten had.

[gnoe93]

Excuses voor de late reactie!
Ik wist niet dat je condities op joins kon zetten. Ik neem aan dat in de meeste gevallen een simpele WHERE hetzelfde bereikt? Enkel als je een specifieke conditie voor een join nodig hebt dus.

Is het trouwens ook niet zo dat de query optimizer in de meeste gevallen joins beter afhandelt dan subqueries qua performance?

DJMaze schreef op vrijdag 30 september 2016 @ 21:49:
Doctrine + Symfony is leuk maar visueel ziet je code er complexer uit dan gewoon zelf eerst een query te bouwen.
Schrijf dus eerst de query volledig en test deze, bouw hem daarna om naar Doctrine als dat moet.
Ja, je werkt niet sneller maar langzamer omdat je nu 2x iets doet dat ook in 1x had gekund.

Ik schrijf zelf bij voorkeur ook gewoon liefst native SQL, maar bij grote projecten is het vaak veel makkelijker werken met een ORM. Wat mijn voorbeelden betreft, heb ik het gewoon uit mijn code gekopieerd.

[ Voor 69% gewijzigd door gnoe93 op 11-10-2016 15:11 ]

[Caelorum]

gnoe93 schreef op dinsdag 11 oktober 2016 @ 15:06:
Excuses voor de late reactie!
Ik wist niet dat je condities op joins kon zetten. Ik neem aan dat in de meeste gevallen een simpele WHERE hetzelfde bereikt? Enkel als je een specifieke conditie voor een join nodig hebt dus.[...]

Nee, niet als je met left joins e.d. gaat werken.

[...]Is het trouwens ook niet zo dat de query optimizer in de meeste gevallen joins beter afhandelt dan subqueries qua performance?[...]

Is afhankelijk van de database engine en de manier waarop de data uit de database komt rollen meten = weten in dit geval.

[Verwijderd]

Ik weet niet of dit werkt in je uiteindelijke taal maar je zou de user de variable 'colors' kunnen laten vullen met een aantal komma gescheiden PK id's en die vervolgens in de WHERE clause van je query pleuren met:

code:

SELECT input FROM CLR_intlist_iter(@colors,','))

[gnoe93]

Verwijderd schreef op donderdag 13 oktober 2016 @ 16:44:
Ik weet niet of dit werkt in je uiteindelijke taal maar je zou de user de variable 'colors' kunnen laten vullen met een aantal komma gescheiden PK id's en die vervolgens in de WHERE clause van je query pleuren met:

code:

SELECT input FROM CLR_intlist_iter(@colors,','))

Als ik me niet vergis is dit specifiek voor MSSQL (ik gebruik MySQL).

[Verwijderd]

gnoe93 schreef op vrijdag 14 oktober 2016 @ 22:23:
[...]


Als ik me niet vergis is dit specifiek voor MSSQL (ik gebruik MySQL).

Je hebt gelijk, dit is inderdaad voor MSSQL. Wellicht biedt MySQL ook zoiets? Ik vind het een verdomd handige werkwijze als ik in SQL werk (99% daarvan is in MSSQL, vandaar)

[HollowGamer]

Is het bij dit soort projecten niet handig om gebruik te maken van een NoSql oplossing?

Je geeft bijvoorbeeld aan dat iedere stoel gecustomized kan worden, maar dit kan elke keer verschillen. Zo kan je bij het ene model 2 kleuren kiezen en bij de andere 3.
De ene stoel heeft mogelijk nog meer bepaalde kenmerken die je wilt filteren.

Voor een project heb ik dit ook zo opgebouwd, en de reden was dat ik anders zoveel koppeltabellen zou krijgen, dan je gek wordt van de queries die je moet schrijven.

Je hebt nog altijd een entiteit Color (met daarin ID's) bijv. en maakt voor ieder product de kenmerken aan ('colors' => '1,2,3,4,5..')) aan. Toegeven dat opzoeken langzamer is, maar in principe zou je met wat cachen en een slimme mapping ver moeten komen.

Als je het helemaal Ajax-achtig wilt doen, schrijf je een API waarmee je eenvoudig alle objecten ophaalt in een JSON die aan je wensen voldoet, en daarmee resultaten ververst. Zie de als voorbeeld.

[ Voor 19% gewijzigd door HollowGamer op 15-10-2016 04:29 ]

[ACM]

pedorus schreef op vrijdag 30 september 2016 @ 22:28:
of met subqueries zou ik zorgen dat ze onafhankelijk zijn en niet afhankelijk zoals hierboven. Dus iets als (OR):

SQL:

select * from chair
where chair_id IN (select chair_id from chair_has_color where color_id=1)
   OR chair_id IN (select chair_id from chair_has_color where color_id=2)

Wat het handigst is, hangt van je db-engine af.

Die waar jij op reageert ogen inderdaad inefficient, maar afhankelijke subqueries hoeven niet trager te zijn. Ze hebben wel weer als voordeel dat ze eenvoudiger op index uitgelezen kunnen worden en ook alleen als het nodig blijkt.

Maar dan zou ik wel deze eerst proberen:

SQL:

select * from chair
where EXISTS (select * from chair_has_color where chair_id = chair.id and color_id=1)
   OR EXISTS (select * from chair_has_color where chair_id = chair.id and color_id=2)

HollowGamer schreef op zaterdag 15 oktober 2016 @ 04:20:
Is het bij dit soort projecten niet handig om gebruik te maken van een NoSql oplossing?

Dit soort searches in een specifieke search-database (zoals elasticsearch) verwerken kan erg krachtig zijn. Maar je moet dan natuurlijk ook die ES-database gaan onderhouden en onderhouden. Dat is mijns inziens pas interessant zodra je 'veel' documenten (in dit geval stoelen) krijgt - ik weet alleen niet zeker wat veel is
Als je kiest voor puur alleen de nosql-database, dan moet je weer goed kijken naar allerlei semantische verschillen. Welke van de ACID-componenten doet je nosql-database niet (goed) en kan je daarmee echt leven? Als je banktransacties doet, dan zijn ze allemaal erg belangrijk... een slecht werkende Atomiciteit of Consistency kan je zomaar miljoenen dollars kosten, zoals sommige bitcoin-partijen hebben ontdekt

Maar in dit voorbeeld zou het wellicht wel goed kunnen werken. Zeker met de wat modernere nosql-databases die wel goed omgaan met ACID, ipv daar elementen van opofferen.

[HollowGamer]

ACM schreef op zaterdag 15 oktober 2016 @ 11:03:
Dit soort searches in een specifieke search-database (zoals elasticsearch) verwerken kan erg krachtig zijn. Maar je moet dan natuurlijk ook die ES-database gaan onderhouden en onderhouden. Dat is mijns inziens pas interessant zodra je 'veel' documenten (in dit geval stoelen) krijgt - ik weet alleen niet zeker wat veel is
Als je kiest voor puur alleen de nosql-database, dan moet je weer goed kijken naar allerlei semantische verschillen. Welke van de ACID-componenten doet je nosql-database niet (goed) en kan je daarmee echt leven? Als je banktransacties doet, dan zijn ze allemaal erg belangrijk... een slecht werkende Atomiciteit of Consistency kan je zomaar miljoenen dollars kosten, zoals sommige bitcoin-partijen hebben ontdekt

Maar in dit voorbeeld zou het wellicht wel goed kunnen werken. Zeker met de wat modernere nosql-databases die wel goed omgaan met ACID, ipv daar elementen van opofferen.

In principe zou je kunnen kiezen voor beide, maar dan lijkt mij inderdaad één enkele NoSql een betere oplossing, zie overzicht.

Wat bedoel je precies met allemaal die termen?
Stel je neemt het volgende voorbeeld (van jenssegers/laravel-mongodb):

PHP:

$product = new Product;
$product->title = 'Stoel 1',
$product->colors = '1,2,3';
$product->price = 50.00;
$product->description = '..';
$product->save();

Je zou nu bijvoorbeeld alle producten kunnen opzoeken met:

PHP:

Product::where('colors', 'all', ['1', '2', '3'])->get();
Product::where('colors', 'or', ['1', '2', '3'])->get();

Er zullen vast non-Laravel oplossingen zijn en er is veel mogelijk.

Je kan ook gewoon queries runnen die elke X refreshen en vervolgens matches ophalen via API:

code:

https://example.com/api/products/color/1
https://example.com/api/products/colors?id=1,2,3

Het voordeel is dat je veel flexibeler bent, maar je moet inderdaad goed opletten dat je niet het overzicht verliest.

[ACM]

Het klassieke voorbeeld bij ACID is de verwerking van geld overmaken van rekening A naar B. Stel dat A 1200 euro heeft staan en B 900 euro en er wordt 100 euro van A naar B overgemaakt. Het verwachtte eindresultaat is dan natuurlijk dat A uitkomt op 1100 euro en B op 1000 euro.

Bij SQL doe je in een triviale oversimplificatie zoiets:

SQL:

SET ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
INSERT INTO transactions (from, to, value) VALUES ('A', 'B', 100);
UPDATE bankAccount SET value = value - 100 WHERE accountId = 'A';
UPDATE bankAccount SET value = value + 100 WHERE accountId = 'B';
COMMIT;

Bij goede atomicity (de A van ACID) en Isolation (de I) is er geen mogelijkheid waarbij als je de beide saldo's steeds bekijkt het iets anders kan zijn dan ofwel 1200 en 900 ofwel 1100 en 1000.

En ook als er vrijwel tegelijk een soortgelijke transactie binnenkomt (bijvoorbeeld ook 100 euro van A naar C), dan is het resultaat steeds hetzelfde; Consistent.

O.a. bij mongodb is het niet mogelijk die drie queries als één geheel uit te voeren. Er is dan dus een moment dat er bijvoorbeeld 1100 en 900 zichtbaar is. Of wellicht zelfs juist 1200 en 1000. Als je dat moment weet te misbruiken - wat bij een paar bitcoin-exchanges is gebeurd - kan je bijvoorbeeld steeds doen alsof er 1200 euro stond en dan gauw 100 euro overmaken vóór het systeem er 1100 van gemaakt heeft.

[HollowGamer]

ACM schreef op zaterdag 15 oktober 2016 @ 13:57:
[..]

O.a. bij mongodb is het niet mogelijk die drie queries als één geheel uit te voeren. Er is dan dus een moment dat er bijvoorbeeld 1100 en 900 zichtbaar is. Of wellicht zelfs juist 1200 en 1000. Als je dat moment weet te misbruiken - wat bij een paar bitcoin-exchanges is gebeurd - kan je bijvoorbeeld steeds doen alsof er 1200 euro stond en dan gauw 100 euro overmaken vóór het systeem er 1100 van gemaakt heeft.

Maar je kan dit toch ook in een wachtrij doen?
Je voert deze uit in een batch en laat bijvoorbeeld enkel een transactie toe om de X minuten.
Of los je daar het probleem niet mee op?

[ACM]

HollowGamer schreef op zaterdag 15 oktober 2016 @ 16:25:
[...]

Maar je kan dit toch ook in een wachtrij doen?
Je voert deze uit in een batch en laat bijvoorbeeld enkel een transactie toe om de X minuten.
Of los je daar het probleem niet mee op?

Het voorbeeld was niet zozeer bedoeld om met een alternatief op te lossen, maar om een belangrijk aspect aan de keuzes van nosql-storage toe te lichten

Het is niet ongebruikelijk dat er wordt gekozen om consistency wat te verlichten om zo eenvoudiger schaalbaar te zijn of anderszins betere performance te bieden. Dat staat ook wel beschreven als de CAP Theorem; je kunt maximaal twee kiezen van Consistency, Availability en Partitioning. De derde zal je minder goed in zijn. Bij de SQL-databases staat die C hoog in het vaandel, gevolgd door de A. Bij NoSQL-databases is het vaak A en P die belangrijk zijn.

Er zijn vast uitwerkingen voor de probleemsituaties te verzinnen waardoor je er minder last van hebt. De toepasbaarheid daarvan wordt uiteraard mede bepaald door zaken als in welke mate 'realtime' noodzakelijk is (aka, je wil direct na het doen van je aanpassing al de aangepaste data kunnen uitlezen) en van de architecturen waar je mee moet (samen) werken.

Met je wachtrij introduceer je overigens natuurlijk wel weer een extra plek waar iets mis kan gaan (verwijder je een bericht voor of na het verwerken? En bij welke je ook kiest, hoe ga je om met crashes van consumers en/of de queue zelf?)

Let wel; de impact van al dat soort problemen bepaald in hoeverre ze echt een probleem voor een specifieke situatie zijn of vooral een mooie theoretische discussie. Voor een bank gelden wat strengere eisen dan voor een forum waar wellicht soms berichten dan in de 'verkeerde volgorde' kunnen verschijnen of af en toe een wijziging niet doorkomt.
En volgens die laatste aannames zijn veel van de 'highly scalable' nosql-systems gemaakt; er zijn er ook die zich vooral richten op een paradigma dat beter past bij bepaalde problemen, maar nog steeds ACID kunnen zijn.

[ Voor 40% gewijzigd door ACM op 15-10-2016 17:04 ]

[pedorus]

ACM schreef op zaterdag 15 oktober 2016 @ 11:03:
Maar dan zou ik wel deze eerst proberen:

exists zie ik meer als een join-type dan als een afhankelijke subquery

Dit soort searches in een specifieke search-database (zoals elasticsearch) verwerken kan erg krachtig zijn.

Gegeven alleen deze taak zou ik inderdaad ook voor solr gaan (of elasticsearch). Mocht MySQL later toch niet snel genoeg blijken dan kan dit altijd nog, maar lijkt me vrij sterk in dit geval.