Using Sequelize how can I specify which field to sort / limit by?

Question

My query is:

    db.Question.findAll
      where:
        id:
          $notIn: if questionIds.length > 0 then questionIds else [-1]
        TopicId: topicCount.id
        PassageId: null
        status: 'active'
        level:
          $lte: startLevel
          $gte: endLevel
      include: [
        model: db.Answer
      ]
      order: [db.Sequelize.fn 'RANDOM']
      limit: questionSections[sectionIndex].goal * 2

And that generates the following query:

SELECT "Question".*, 
       "answers"."id"         AS "Answers.id", 
       "answers"."answertext" AS "Answers.answerText", 
       "answers"."iscorrect"  AS "Answers.isCorrect", 
       "answers"."createdat"  AS "Answers.createdAt", 
       "answers"."updatedat"  AS "Answers.updatedAt", 
       "answers"."questionid" AS "Answers.QuestionId" 
FROM   (SELECT "Question"."id", 
               "Question"."status", 
               "Question"."questiontext", 
               "Question"."level", 
               "Question"."originalid", 
               "Question"."createdbyuserid", 
               "Question"."editedbyuserid", 
               "Question"."createdat", 
               "Question"."updatedat", 
               "Question"."instructionid", 
               "Question"."topicid", 
               "Question"."subjectid", 
               "Question"."passageid" 
        FROM   "questions" AS "Question" 
        WHERE  "Question"."id" NOT IN ( -1 ) 
               AND "Question"."topicid" = '79' 
               AND "Question"."passageid" IS NULL 
               AND "Question"."status" = 'active' 
               AND ( "Question"."level" <= 95 
                     AND "Question"."level" >= 65 ) 
        LIMIT  300) AS "Question" 
       LEFT OUTER JOIN "answers" AS "Answers" 
                    ON "Question"."id" = "answers"."questionid" 
ORDER  BY Random(); 

That's all well and good, except I want the ORDER BY to apply to the inner Query (SELECT "Question"."id", "Question"."status",). How can I achieve this?

Answer 1

Have you tried defining custom scope, say random, that orders the questions randomly and then using that scope in your query like:

db.Question.scope('random').findAll({
  where:{
    ..........
  },
  include: [{
    model: db.Answer
  }],
  //order: [db.Sequelize.fn 'RANDOM'] <<<<<<< moved to custom scope
  limit: questionSections[sectionIndex].goal * 2
 })