[PHP]Relevantie zoekresultaten - hulp bij denkwijze

[MisterEagle]

Hallo,

Laat ik voorop stellen dat ik al enige tijd niets meer met PHP/MySQL heb gedaan, maar dat ik niet zo zeer opzoek ben naar hulp qua code. Wel zou ik een andere denkwijze horen voor een uitdaging waar ik tegenaan loop.

Ik hoop de situatie zo het duidelijkste uit te kunnen leggen.
In de database zitten 2 tabellen.
Ideeën
idee_id
idee_gebieden
idee_naam

en
gebieden
gebied_id
gebied_naam

een idee kan betrekking hebben op meerdere gebieden, zodoende dacht ik het veld "idee_gebieden" te vullen met "gebied_id; gebied_id" etc.

Het enige zoekfilter bestaat uit checkboxen met "gebied_naam" erachter. Dit is dus dynamisch. Iedere nieuw toegevoegd gebied komt er automatisch bij.
Het is niet de bedoeling dat er een limiet komt op de hoeveelheid checkboxen welke je aan kan vinken.

Nu dacht ik zelf om dan het volgende te doen:
1) na het submitten van het filter tel hoeveel vinkjes er zijn gezet en creëer net zoveel variabelen (1 t/m aantal vinkjes)
2) haal de hele "ideeen" tabel op
3) ga per record na hoeveel deze matched en plaats deze is de juiste variabele van hoeveel het idee matched (1 t/m aantal vinkjes)
4) display vervolgens de aangemaakte variabele waarbij het hoogste gedaan eerst.

Nu is dit vast niet de mooiste aanpak (en dat hoeft ook niet persé) maar mijn uitdaging zit hem nu in het feit dat ik het niet voor elkaar krijg om een variabel aantal variabelen aan te maken.

Tevens zit ik hier al even over na te denken en ik kan me geen andere manier bedenken hoe dit aan te pakken is.

Ik hoop dat iemand me een beetje in de goede richting kan sturen of de gouden tip heeft!

[RobIII]

Keyword hier is n:m (ook wel many-to-many, veel-op-veel) relaties

[ Voor 59% gewijzigd door RobIII op 02-04-2014 17:47 ]

[Hydra]

Ten eerste: het is gewoon verkeerd om meerdere ID's op die manier in 1 kolom op te gaan slaan. De database kan niet indexen op een dergelijke string en je kunt geen foreign-key relaties aanleggen.

Als je gewoon een koppeltabel (idee_gebied) met een idee_id en gebied_id gebruikt, kun je hier gewoon op filteren.

Dus: in je filterlijst haal je gewoon een lijst met gebieden op. Dan laat je iemand X gebieden aanvinken. Stel iemand heeft gebied_id 1,5 en 200 aangevinkt. Dan is je query iets in de trant van:

SELECT i.idee_id, i.idee_titel FROM idee i INNER JOIN idee_gebied ig ON ig.idee_id = i.idee_id WHERE ig.gebied_id IN (1,5,200)

Klaar. Zo ga je om met N:M relaties, je gaat never nooit niet meerdere IDs in 1 kolom proppen.

FF nog iets kwa naamgeving: het is over het algemeen bad practice om de naam van de tabel in de kolomnamen te herhalen. Het is overbodig aangezien ID in tabel "idee" al het id van een idee record hoort te zijn. Voor een kolom met een foreign-key relatie gebruik je meestal <tabel>_id

Stel je gebruikt een meervoudsvorm voor jouw tabellen dan ziet het er ff zo uit (in het engels, van Nederlandse tabelnamen krijg ik jeuk ):

ideas:
- id
- title
- ...

areas:
- id
- title
- ...

ideas_areas:
- id
- idea_id
- area_id

[ Voor 66% gewijzigd door Hydra op 02-04-2014 17:50 ]

[MisterEagle]

Beide bedankt voor jullie reacties! hier kan ik weer mee verder!

[MisterEagle]

sorry voor de bump maar ik loop hier toch nog een stukje in vast namelijk op het volgende

inmiddels weet ik hoeveel vinkjes de gebruiker heeft gezet en ik kan alle ideeën weergeven die raking hebben met een gebied.
Echter, het gebied hoeft slechts 1 van de ideeën te raken om weergegeven te worden, maar hoe kan ik nu dan de relevantie bepalen? Kan dit in de query al, of moet ik dit erna doen door het aantal keer dat een idee naar voren komt te vergelijken met het aantal vinkjes wat is gezet?
(als in aantal vinkjes = 100%, hoeveel vinkjes matched het en dan berekenen)

[Woy]

Je wil de data aggregeren, dan kun je gebruik maken van GROUP BY en een aggregate functie zoals COUNT, en eventueel achteraf sorteren met ORDER BY

[MisterEagle]

En dat kan in een enkele query?
Dan krijg ik dus iets van

SELECT i.idee_id, i.idee_titel, COUNT(ig.idee_id) AS relevantie FROM idee i INNER JOIN idee_gebied ig ON ig.idee_id = i.idee_id WHERE ig.gebied_id IN (1,5,200) GROUP BY ig.idee_id ORDER BY relevantie DESC

SELECT i.idee_id, i.idee_titel, COUNT(ig.idee_id) AS relevantie FROM idee i INNER JOIN idee_gebied ig ON ig.idee_id = i.idee_id WHERE ig.gebied_id IN (1,5,200) GROUP BY ig.idee_id ORDER BY COUNT(ig.idee_id) DESC

Vanavond even proberen op de db, maar in een testomgeving op w3s lijkt dit wel te werken.

thanks!

[ Voor 32% gewijzigd door MisterEagle op 15-04-2014 15:44 ]

[NMe]

Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?