MySQL join and multiple "order by"

[Firas Durri]

Hi,

Was looking for some help with this query.

I've also posted this question at http://stackoverflow.com/questions/35580937/mysql-join-order-by-rand-then-sort-asc-preferably-using-sequelize

My tables are:

Questions: id, question

Answers: id, question_id, answer

My Sequelize code is:

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'],

    }]

})

With not_in set to -1, Sequelize generates this query:

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 results in:

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 would like this result, but with the question sorted randomly.

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

Would appreciate any pointers for how to get such a result. Thanks!

[Mick Hansen]

How would you express that order in SQL?

--

Mick Hansen

@mhansendev
mhansen.io

[Firas Durri]

I'm afraid I'm not sure! I assumed it would be something simple that I don't quite know how to implement.

Basically, the JSON result I get using Sequelize/node.js is pasted below. I want the questions to be in random order without having to shuffle the array in JavaScript.

{
   "questions":[
      {
         "id":13,
         "question":"first question",
         "answers":[
            {
               "id":17,
               "question_id":13,
               "answer":"1st answer"
            },
            {
               "id":23,
               "question_id":13,
               "answer":"2nd answer"
            },
            {
               "id":24,
               "question_id":13,
               "answer":"3rd answer"
            }
         ]
      },
      {
         "id":14,
         "question":"Second question",
         "answers":[
            {
               "id":18,
               "question_id":14,
               "answer":"1st answer"
            },
            {
               "id":21,
               "question_id":14,
               "answer":"2nd answer"
            },
            {
               "id":22,
               "question_id":14,
               "answer":"3rd answer"
            }
         ]
      },
      {
         "id":15,
         "question":"Third question",
         "answers":[
            {
               "id":19,
               "question_id":15,
               "answer":"1st answer"
            },
            {
               "id":20,
               "question_id":15,
               "answer":"2nd answer"
            }
         ]
      }
   ]
}

I tried adding Sequelize.fn('RAND') to the order part, .e.g:

order: [[models.sequelize.fn('RAND'), ['id','ASC'], [models.answers, 'id', 'ASC']],

But it ends up shuffling the answers as well.

[Mick Hansen]

The second order statement is only ever used if the first order statement results in two rows with the same order index.
With rand() that is never the case.

[Firas Durri]

Someone posted this on Stack Overflow as a plain MySQL answer, it seems to work

SELECT  questions.id, questions.question, answers.id as `answers.id`, answers.question_id as `answers.question_id`, answers.answer as `answers.answer`
    FROM  
      ( SELECT  RAND() AS rnd, id FROM questions where questions.id NOT IN (15) ) AS r
    JOIN  questions AS questions ON questions.id = r.id
    JOIN  answers AS answers   ON answers.question_id = questions.id
    ORDER BY  r.rnd, answers.id

Will report back after I try to implement express this in Sequelize... any ideas appreciated.

[Mick Hansen]

That would work, not expressable with sequelize i'm afraid though.