Geek Answers Handbook

MySQL joins, ORDER BY RAND () then sorts ASC (preferably using Sequelize)

I need help with this MySQL query. Ideally, I would generate it using node.js Sequelize ORM.

Tables:

Questions: id, question Answers: id, question_id, answer

My Sequelize Code:

 models.questions.findAll({ where: { id: { $notIn: not_in } }, order: [['id','ASC'], [models.answers, 'id', 'ASC']], attributes: ['id', 'question'], include: [{ model: models.answers, attributes: ['id', 'question_id', 'answer'], }] })

If not_in set to -1 , Sequelize generates this request:

 SELECT `questions`.`id`, `questions`.`question`, `answers`.`id` AS `answers.id`, `answers`.`question_id` AS `answers.question_id`, `answers`.`answer` AS `answers.answer` FROM `questions` AS `questions` LEFT OUTER JOIN `answers` AS `answers` ON `questions`.`id` = `answers`.`question_id` WHERE `questions`.`id` NOT IN ( -1 ) ORDER BY `questions`.`id` ASC, `answers`.`id` ASC

And the results:

 id | question | answers.id | answers.question_id | answers.answer 13 | first question | 17 | 13 | 1st answer 13 | first question | 23 | 13 | 2nd answer 13 | first question | 24 | 13 | 3rd answer 14 | second question | 18 | 14 | 1st answer 14 | second question | 21 | 14 | 2nd answer 14 | second question | 22 | 14 | 3rd answer 15 | third question | 19 | 15 | 1st answer 15 | third question | 20 | 15 | 2nd answer

I need this result, but with a question sorted randomly.

So, instead of 13, 14, then 15, it could be 14, 15, 13, but the answers still match their question and are sorted by answers.id .

You will need any Sequelize or MySQL code pointers to get this result. Thanks!

I tried adding ORDER BY RAND() in different places, but ended up shuffling the answers too.

PS Aside, earlier, I needed this with one randomly selected question for which I used:

 SELECT `questions`.`id` AS `question_id`, `questions`.`question` AS `question`, `answers`.`id` AS `answer_id`, `answers`.`answer` AS `answer` FROM (SELECT `questions`.`id`, `questions`.`question` FROM `questions` AS `questions` WHERE (SELECT `question_id` FROM `answers` AS `answers` WHERE `questions`.`id` = `answers`.`question_id` AND questions.id NOT IN ( -1 ) LIMIT 1) IS NOT NULL ORDER BY RAND() LIMIT 1) AS `questions` INNER JOIN `answers` AS `answers` ON `questions`.`id` = `answers`.`question_id` ORDER BY `answers`.`question_id`, `answers`.`id`

which will return, for example:

 id | question | answers.id | answers.question_id | answers.answer 14 | second question | 18 | 14 | 1st answer 14 | second question | 21 | 14 | 2nd answer 14 | second question | 22 | 14 | 3rd answer
+6
source share
1 answer

In plain MySQL:

 SELECT ... FROM ( SELECT RAND() AS rnd, id FROM questions ) AS r JOIN questions AS q ON q.id = r.id JOIN answers AS a ON a.question_id = q.id ORDER BY r.rnd, a.id
+1
source

More articles:


All Articles