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

Question

My request:

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 generates the following request:

 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(); 

This is all good and good, except that I want ORDER BY apply to the internal query ( SELECT "Question"."id", "Question"."status", ). How can I achieve this?

Answer 1

You tried to define a custom scope , say random , which randomizes the questions and then uses this scope in your query as:

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