mysql random questions

Question

I have a somewhat complex question and I need help:

I have a database with the following links:

Exemplifying: I am in the history class about Karl Marx. This class is linked to a certain group of Karl Marx’s "subject" which has a 100 question bank. However, of those 100, I already answered 50 ("questoes_control").

The purpose of the problem is: Generate only 5 random questions according to the selected class that I have not yet answered (each question has 5 answers).

arrived in query:

SELECT aulas.nomeaula, 
       assuntos.nomeassunto, 
       questoes.questao, 
       respostas.resposta 
FROM aulas 
INNER JOIN aulas_has_assuntos ON (aulas.idaulas = aulas_has_assuntos.aulas_idaulas) 
INNER JOIN assuntos ON (aulas_has_assuntos.assuntos_idassuntos = assuntos.idassuntos) 
LEFT JOIN (SELECT * FROM assuntos_has_questoes WHERE NOT EXISTS(SELECT * FROM questao_control WHERE assuntos_has_questoes.questoes_idquestoes = questao_control.questoes_idquestoes AND questao_control.usuarios_idusuarios=1) ORDER BY RAND() LIMIT 5) a ON (aulas_has_assuntos.assuntos_idassuntos = a.assuntos_idassuntos) 
LEFT JOIN questoes ON (a.questoes_idquestoes = questoes.idquestoes) 
LEFT JOIN respostas ON (questoes.idquestoes = respostas.questoes_idquestoes)
WHERE aulas.idaulas=2070

Any idea?

Answer 1

One way to do it (not advisable in tables with many records): use the rand() and LIMIT:

SELECT aulas.nomeaula, 
       assuntos.nomeassunto, 
       questoes.questao, 
       respostas.resposta 
FROM aulas 
INNER JOIN aulas_has_assuntos ON (aulas.idaulas = aulas_has_assuntos.aulas_idaulas) 
INNER JOIN assuntos ON (aulas_has_assuntos.assuntos_idassuntos = assuntos.idassuntos) 
LEFT JOIN (SELECT * FROM assuntos_has_questoes WHERE NOT EXISTS(SELECT * FROM questao_control WHERE assuntos_has_questoes.questoes_idquestoes = questao_control.questoes_idquestoes AND questao_control.usuarios_idusuarios=1) ORDER BY RAND() LIMIT 5) a ON (aulas_has_assuntos.assuntos_idassuntos = a.assuntos_idassuntos) 
LEFT JOIN questoes ON (a.questoes_idquestoes = questoes.idquestoes) 
LEFT JOIN respostas ON (questoes.idquestoes = respostas.questoes_idquestoes)
WHERE aulas.idaulas=2070
ORDER BY rand() 
LIMIT 5

You are limiting your search to 5 records (LIMIT 5) and randomly ordering the search (rand()).

---

Example of random query, with performance:

SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT 1 OFFSET ', @r);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

You arrow the variable r, makes a draw using the amount of table records pulled by COUNT(*).

Concatenates the select with the drawn number, and executes the query.

Source

+ Link