javascript - 使用 Sequelize 如何指定要排序/限制的字段？

Question

我的查询是:

    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

然后生成以下查询:

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

一切都很好，只是我希望将 ORDER BY 应用于内部查询 (SELECT "Question"."id", "Question"."status",)。我怎样才能做到这一点？

Answer 1

您是否尝试过定义自定义 scope ，比如 random，它随机排列问题，然后在您的查询中使用该范围，例如:

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