[MYQSL] Random rij uit een tabel kiezen, zonder bepaald Id#

[Joske Pauwels]

Hey,

Ik ben een site aan het maken waarbij een groep testers quizvragen kunnen testen. Twee van mijn mysql tabellen zijn relevant voor dit probleem:

1. Er is een tabel met als entries allemaal verschillende quizvragen (allerlei velden met de vraagstelling, het antwoord, etc...)
2. Een tabel met "evaluaties". Wanneer een gebruiker een vraag oplost, wordt de evaluatie hierin opgeslagen. Één veld slaat het id nr van de gebruiker op (opgeslagen als PHP sessie variabele), en een ander veld het id nummer van de vraag (uit de tabel met vragen). Voor de rest wordt er een persoonlijke waardering van de gebruiker voor de vraag etc opgeslagen. Dmv deze evaluaties kunnen we te moeilijke/gemakkelijke vragen schrappen en dmv SPSS statistisch afleiden welke vragen goed scoren bij bepaalde doelgroepen.

Mijn probleem zit bij het presenteren van een nieuwe vraag aan de gebruiker. Ik wil hem een willekeurige nieuwe vraag voorschotelen. Er moet dus een random vraag geselecteerd worden, uit de lijst van vragen die nog niet door de bewuste gebruiker opgelost (=geëvalueerd) mag zijn. (Dus in de tabel met evaluaties mag nog geen record voorkomen met deze gebruiker en de nieuwe vraag)

Nu, het enige dat ik voorlopig kan bedenken is willekeurig een vraag kiezen, en dan kijken of de gebruiker die al opgelost heeft. Zo neen: een andere vraag kiezen en opnieuw proberen enz...
Dit is echter niet zo efficient, zeker gezien de vragentabel later toch +1000 records gaat bevatten, en de evaluatietabel tot 50 keer zoveel. (50 testgebruikers x 1000 vragen = potentieel 50 000 evaluaties).

Is hier een efficientere methode voor? Wss door middel van tabellen te koppelen in mysql. Of raden jullie een compleet ander design van de database aan?

Alvast bedankt!

[ Voor 10% gewijzigd door Joske Pauwels op 11-10-2006 01:49 ]

[NMe]

Middels een outer join kun je de beide tabellen met elkaar joinen, en die records waarin het id van een vraag NULL is in de gejoinde resultset zijn nog niet voorbij gekomen voor de gebruiker. Je hoeft dus alleen maar één random record te selecteren (ORDER BY RAND() LIMIT 1) waarbij dat veld na joinen NULL is.

[Joske Pauwels]

Ok, zal ik proberen uitzoeken. En een vraagje terzijde: kan een normale server zulk een request aan als er zoveel (zie topic-start-post) entries zijn?

[Joske Pauwels]

Ok, op deze manier is het probleem gedeeltelijk opgelost. Er rest echter nog bijkomend probleem.

Voorbeeld: gebruiker 1 heeft vragen 1 en 2 reeds afgelegd
gebruiker 2 heeft vragen 2 en 3 reeds afgelegd.
Er zijn dus 4 entries in de evaluatie-tabel,
en bijvoorbeeld 4 vragen in de vragen-tabel.

Wanneer ik nu voor gebruiker 1 een query maak die de evaluaties die NULL zijn en tegelijk niet door gebruiker 1 zijn afgelegd selecteer, komt vraag 2 er toch bij.

Reden: er zitten twee evaluaties van vraag 2 in de evaluatietabel, en de evaluatie door een andere gebruiker betekent niet dat die vraag al afgelegd is door de huidige gebruiker.

De geselecteerde vraag uit de gejoinde tabel zou dus aan deze voorwaarden moeten doen:

• Ofwel is de vraag door niemand al afgelegd
• Ofwel reeds door iemand anders afgelegd, maar niet door gebruiker zelf.

Het is met dat tweede punt dat ik problemen heb, want ik krijg dit niet in een enkel where statement geperst. Op één of andere manier moet hij dus voor elke evaluatie door iemand anders die hij overloopt, checken of er al geen evaluatie van die vraag bestaat door de huidige gebruiker.

Is dit enorm simpel en zie ik er compleet naast, of is dit tricky business?

[ Voor 16% gewijzigd door Joske Pauwels op 11-10-2006 03:26 ]

[NMe]

Laat je query eens zien?

[Reptile209]

Je zou 'm ook andersom kunnen doen: maak bij het aanmaken van een nieuwe gebruiker een serie entries in je evaluatietabel voor alle vragen: kolom "answered" op false. Elke keer als een gebruiker een vraag beantwoord heeft, gooi je die entry op true. Een nieuwe vraag selecteren is dan een random selectie uit gebruiker + answered = false in de evaluatietabel. 't Is alleen wat meer werk bij het aanmaken van een gebruiker en als je een vraag toe zou willen voegen.

[moozzuzz]

Ik dacht eigenlijk spontaan aan een NOT IN statement, maar herinner me dat MySQL niet zo fantastisch is in subqueries. Weet niet of jouw versie die al ondersteund?

Je gebruiker-prob: dat is een kwestie van de juiste te joinen gegevens neen?

code:

... JOIN ON vragen van één user...

[Joske Pauwels]

Dit is mijn query:

code:

SELECT quizmasters_vragen.id AS vraag
FROM quizmasters_vragen LEFT OUTER JOIN quizmasters_evaluaties
ON (quizmasters_vragen.Id=quizmasters_evaluaties.vraag)
WHERE (quizmasters_evaluaties.tester != '$gebruikers_nr'
OR quizmasters_evaluaties.tester IS NULL);

@ Reptile209 : ik begrijp je idee, en heb het zelf overwogen, maar ik denk dat het de database nodeloos groot en traag zal maken. Met dik 1000 vragen (en het kunnen er nog meer worden) en 80 users krijg je dan al gauw 80 000 entries... Dus als je dan een nieuwe gebruiker registreert, moeten er al meteen 1000 entries in die evaluatietabel aangemaakt worden, terwijl hij er misschien maar 100 gaat doen...

@ moozzuzz: de MySQL versie op mijn server is 4.1.19 (wordt niet door mij beheerd, kan die dus niet upgraden). Om lokaal te testen gebruik ik 3.23.49, maar die kan ik eventueel upgraden.

Samengevat is dit dus mijn probleem: ik wil in de gejoinde tabel (vragen gejoined met evaluaties) alle vragen selecteren die nog niet door de gebruiker geëvalueerd zijn. Dus als er een entry bestaat van de huidige gebruiker die pakweg vraag x heeft geëvalueerd, moeten evaluaties door andere gebruikers van diezelfde vraag genegeerd worden...

[Siliakus]

In plaats van in elke query die join maken zou ik een view definieren op de relevante kolommen in beide tabellen. Performance-wise is dit al een flinke stap vooruit!

ps. ik denk nog even na over je where-clause probleem

[ Voor 15% gewijzigd door Siliakus op 11-10-2006 21:15 ]

[Joske Pauwels]

Siliakus schreef op woensdag 11 oktober 2006 @ 20:51:
In plaats van in elke query die join maken zou ik een view definieren op de relevante kolommen in beide tabellen. Performance-wise is dit al een flinke stap vooruit!

ps. ik denk nog even na over je where-clause probleem

Euh, een view dat zegt me echt niets, maar ik zal er eens naar zoeken.

En laat het zeker weten als je een oplossing hebt voor het "where" probleem!

[Joske Pauwels]

Ik heb er nog wat over zitten nadenken: ik zou wel dmv PHP een superlang where statement kunnen maken dat al die bewuste vragen uitsluit, maar dat lijkt me hyperomslachtig.

Dan zou ik eerst in de evaluatiedatabase mits een query alle vragen opzoeken die al afgelegd zijn, en dan in mijn "echte" query, een lange where erachter zetten met "vraag != 3 AND vraag != 5 AND vraag != 32" enzovoort.

Probleem is dan dat voor iemand die al 400 vragen afgelegd heeft, er 400 van die voorwaarden in de query moeten.


Een ander idee dat ik had: in de tabel met vragen, maar ik een nieuwe kolom (type text ofzo). Daarin zet ik de id-nrs van iedereen die de bewuste vraag al afgelegd heeft. Dus als gebruikers 3, 32 en 67 de vraag al hebben gedaan, dan staat erin: "-3- -32- -67- ". Om een nieuwe vraag te kiezen, neem ik er gewoon eentje uit de vragentabel, waarvan de usernummer niet in de vorm "-usernr-" voorkomt in die kolom. De evaluatiekolom is dan enkel om uitgebreide info over de evaluaties te weten (Hoe scoorden ze, wat is de waardering, opmerkingen...) Maar is dat ook niet een omslachtige methode??

Hier moet toch een oplossing voor bestaan? het is toch niet zo'n exotische toepassing, of wel?

[ Voor 32% gewijzigd door Joske Pauwels op 11-10-2006 23:07 ]

[Reptile209]

Joske Pauwels schreef op woensdag 11 oktober 2006 @ 19:18:
@ Reptile209 : ik begrijp je idee, en heb het zelf overwogen, maar ik denk dat het de database nodeloos groot en traag zal maken. Met dik 1000 vragen (en het kunnen er nog meer worden) en 80 users krijg je dan al gauw 80 000 entries... Dus als je dan een nieuwe gebruiker registreert, moeten er al meteen 1000 entries in die evaluatietabel aangemaakt worden, terwijl hij er misschien maar 100 gaat doen...

Aha, ik las het meer als een soort enquete (met een beperkte set vragen die om een of andere duistere reden willekeurig gesteld moesten worden). Zoals je het nu stelt, is mijn idee inderdaad niet handig (ook al niet omdat je er vast nog wel een keer extra vragen aan toe wilt voegen).

Samengevat is dit dus mijn probleem: ik wil in de gejoinde tabel (vragen gejoined met evaluaties) alle vragen selecteren die nog niet door de gebruiker geëvalueerd zijn. Dus als er een entry bestaat van de huidige gebruiker die pakweg vraag x heeft geëvalueerd, moeten evaluaties door andere gebruikers van diezelfde vraag genegeerd worden...

Ik probeer een redenatie voor je op te zetten waar je zelf de SQL bij moet fantaseren. Mischien helpt het...
1. je hebt twee tabellen. Ik noem ze even Vragen en Beantwoord. Vragen bevat een sloot met vragen inkoppertje met elk een eigen ID en Beantwoord bevat user ID's en vraag ID's voor iedere vraag die een user beantwoord heeft. De rest is even clutter wat niks met je probleem te maken heeft.
2. Je wil een vraag (ID) kunnen selecteren uit Vragen die nog niet door UserID beantwoord is, dus die nog niet in Beantwoord voorkomt.

Je query zou dus een tabel moeten maken waarin Vragen ID's gekoppeld worden aan UserID's (door overeenkomstige VragenID's). Koppel dus beide tabellen op VraagID en selecteer daaruit degenen die het juiste UserID hebben. Dat geeft je een lijst (tabel AlKlaar) met beantwoorde vragen (VraagID) voor die user (UserID).
In een volgende stap koppel je Vragen en AlKlaar aan elkaar, bijvoorbeeld door Vragen (in een tijdelijke tabel) uit te breiden met een true/false die aangeeft of een vraag in AlKlaar voorkomt (en dus al geweest is). Selecteer tot slot een willekeurige VraagID die die vlag op false heeft staan. Of pak meteen de "false" lijst door daar te selecteren op vragen die niet in AlKlaar voor komen (Vragen.VraagID <> AlKlaar.VraagID)

Probeer het desnoods handmatig en stap voor stap, zodat je kunt controleren of je query klopt. Probeer het indien nodig uit op tabellen die je handmatig met de genoemde/benodigde info gevuld hebt. Dat werkt een stuk makkelijker dan meteen een mega-query te brouwen want dan zie je niet meer waar het mogelijk mis gaat.
Voeg als alles werkt pas de kolommen met "clutter data" toe, want dat zou dan een eitje moeten zijn.

* Reptile209 is niet zo goed in SQL, maar volgens mij klopt mijn redenatie aardig en mag TS of iemand anders dat in code gieten

[Joske Pauwels]

Dat lijkt me inderdaad de oplossing. Ik had echter geen idee dat er zoiets bestond als een tijdelijke tabel maken.

Dus ik maak eerst (op basis van de evaluaties-tabel) een lijst met vragen die de gebruiker al wel beantwoord heeft, en dan maak ik een soort tijdelijke tabel op basis van deze lijst en de tabel met vragen, waarin reeds beantwoorde vragen bvb met "1" worden gelabeld. En uit de overgebleven vragen kies ik dan een random vraag om aan de gebruiker voor te leggen...

Nu, weet iemand of dit volledig in SQL kan, of moet ik met php die lijst van beantwoorde vragen in een nieuwe query gieten? Ik heb al wat zitten zoeken in de MySQL manual, maar die is zo enorm, dat je er meteen in verloren loopt. Weet er iemand bvb de namen van de relevante commando's? Dan zoek ik zelf wel uit hoe ik ze moet toepassen etc.

Tnx! GoT is echt een redder in nood!

[rickpastoor]

Ik heb geen idee of dit werkt, maar ik doe een gooi:

SQL:

SELECT * FROM vragen WHERE vraagID NOT IN (SELECT vraagID FROM evaluaties WHERE userID = ' . $_SESSION['userID'] . ') ORDER BY RAND() LIMIT 1;

Ik haal dus de vragen op waarop de huidige user geen evaluaties heeft...

[Tanuki]

-NMe- schreef op woensdag 11 oktober 2006 @ 01:49:
Middels een outer join kun je de beide tabellen met elkaar joinen, en die records waarin het id van een vraag NULL is in de gejoinde resultset zijn nog niet voorbij gekomen voor de gebruiker. Je hoeft dus alleen maar één random record te selecteren (ORDER BY RAND() LIMIT 1) waarbij dat veld na joinen NULL is.

Mag ik daar even deze link aan toevoegen?

EDIT: Geldt ook voor Rick Pastoor hierboven, trouwens.

[ Voor 5% gewijzigd door Tanuki op 12-10-2006 00:22 ]

[rickpastoor]

Mja..dat random gebeuren lijkt me niet het meest lastige van het probleem..dat is misschien nog wel makkelijker via php te doen. En trouwens..hij heeft volgens zijn startpost toch maar ongeveer 50 vragen in de database staan, dus dan kan die RAND() functie best
Leer lezen...

[ Voor 6% gewijzigd door rickpastoor op 12-10-2006 00:27 ]

[Joske Pauwels]

@ Rick pastoor: enorm bedankt!! Die truc met NOT IN doet perfect wat ik zocht!

@ l0c4lh0st: lijkt me een interessant idee, maar denk dat het hier niet werkt. Volgens de tekst in je link gaat het enkel als er geen id's ontbreken, en dat is wel het geval. Het kan immers dat een aangemaakte vraag verwijderd wordt. Of mis ik hier iets?

Bedankt in elk geval aan iedereen! Wie nog tips heeft om die random functie te optimaliseren, altijd welkom!

Got = God!

[Exterazzo]

Hij heeft er ook een query tussen staan hoe het werkt met deleted rows:

adding holes to the numbers

The generalize the last solution we add the possibility of holes also known as deleted rows.

code:

SELECT name
  FROM random AS r1 JOIN 
       (SELECT ROUND(RAND() * 
                     (SELECT MAX(id) 
                        FROM random)) AS id)
        AS r2 
 WHERE r1.id >= r2.id 
 ORDER BY r1.id ASC 
 LIMIT 1;

code:

+----+-------------+------------+--------+------+------------------------------+
| id | select_type | table      | type   | rows | Extra                        |
+----+-------------+------------+--------+------+------------------------------+
|  1 | PRIMARY     | <derived2> | system |    1 |                              |
|  1 | PRIMARY     | r1         | range  |  689 | Using where                  |
|  2 | DERIVED     | NULL       | NULL   | NULL | No tables used               |
|  3 | SUBQUERY    | NULL       | NULL   | NULL | Select tables optimized away |
+----+-------------+------------+--------+------+------------------------------+

The JOIN now adds all the IDs which are greater or equal than our random value and selects only the direct neighboor if a direct match is not possible. BUT as soon as one row is found, we stop (the LIMIT 1). And we read the rows according to the index (ORDER BY id ASC).

[Siliakus]

Ik heb geen idee hoe ik iets in een speciaal code-blok zet, dus excuus voor de onoverzichtelijkheid:

SELECT vraag_id
FROM vragen
LEFT JOIN evaluaties ON vragen.vraag_id = evaluaties.vraag_id
WHERE evaluaties.vraag_id IS NULL
OR ( evaluaties.vraag_id IS NOT NULL
AND evaluaties.gebruikers_id != $variable )

Om het jezelf makkelijk te maken zou je een tweetal views kunnen maken:

1: Een view waarin alle vragen staan waarop nog helemaal niet gereageerd is

CREATE VIEW not_evaluated
AS
SELECT vraag_id, vraagstelling
FROM vragen
LEFT JOIN evaluaties ON vragen.vraag_id = evaluaties.vraag_id
WHERE evaluaties.vraag_id IS NULL

2: Een view waarin alle vragen staan waarop al gereageerd is

CREATE VIEW evaluated
AS
SELECT vraag_id, vraagstelling
FROM vragen
LEFT JOIN evaluaties ON vragen.vraag_id = evaluaties.vraag_id
WHERE evaluaties.vraag_id IS NOT NULL

Dan krijg je ook een tweetal queries, eerst één om te kijken of er nog vragen zijn die helemaal niet beantwoord zijn (lichte voorkeur voor eerst zo veel mogelijk vragen beantwoord krijgen?? ) en de ander om te kijken wel vragen nog niet door de specifieke user beantwoord zijn. Indien er nog vragen open staan zonder evaluatie @ all, kan je dan de tweede query overslaan.

Eerste query:

SELECT vraag_id, vraagstelling
FROM not_evaluated

Tweede query:

SELECT vraag_id, vraagstelling
FROM not_evaluated
WHERE not_evaluated.gebruikers_id != $variable


Note: Als je gebruik maakt van deze opzet ben je verplicht evaluaties.vraag_id als NOT NULL te declareren! Anders gaat de gehele vlieger niet op

PS. Syntax kan niet helemaal kloppen, ben Oracle gewend.


Dusss, volgens mij kom je hier een heel eind mee. Maar commentaar is altijd welkom, ga er graag nog even mee verder. Vind het een goede oefening