mysql random questions

Question

mysql random questions

Navigation

#1 by (0 votes)

1

I have a somewhat complex and helpful question:

I have a database with the following links:

Exemplifying:IaminhistoryclassonKarlMarx.Thisclassislinkedtoacertain"subject" group of Karl Marx that has a bank of 100 questions. However, of those 100, I already replied 50 ("questoes_control").

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

I got the 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 ideas?

mysql query

asked by anonymous 20.03.2018 / 06:35

1 answer

How to structure a good controller with Laravel Socialite? Sonar: Validate changes from a certain date

score 0 · Accepted Answer

A way of doing (not advisable on tables with many records): use 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 sorting the search randomly ( rand() ).

Example of a 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 , make a draw using the amount of records in the table pulled by COUNT(*) .

Concatenate select to the number drawn, and execute the query.

Font

+ Link