ruby-on-rails - 如何使用 POSTGRES 在行之间循环以一次输出一个随机的 "something"？

Question

我正在构建一个测验应用程序，并且我有一个表格，其中包含属于某个类别的问题。每个类别都有自己的 ID 号。我想不出一种方法来遍历表格，一次给我一个随机问题。如果有人知道可能的解决方案，请分享。谢谢!

问题 Controller

class QuestionnairesController < ApplicationController

def index

    @question = Questionnaire.find(params[:category_id])
    @category = Category.find(params[:category_id])
    @videos = VideoClue.find(params[:category_id])
    ###This finds all the questions from the question table by their category_id. Whenever I select a category, it matches the question related to the category

    render :show
    ###render :show Renders Html page
end

def choose_answer

    @question = Questionnaire.find(params[:id])
    @choices = @question.choices
    #list all from the choices column

    render :choose_answer

end

def results

    @question_data= Questionnaire.where(id: params[:id])
    @correct_answer = @question_data[0].correct_answer
    @selected_answer = params[:choice]
    #In order to compare the user selected answer to the right answer, I had to make 'choice' as a param and created a variable that is equal to the params so it will render the success and error pages correctly.

    if @selected_answer == @correct_answer
        render :success
    else
        render :error
    end

显示问题页面

<style>
body {
background-color: black;
}

h1 {
color: white;
}
</style>

<center>
<img src = 'http://i.imgur.com/dz0FFLy.png' height="200" width="500" />

</center>



<center>


<br>
<br>
<h1><%=@question.question%></h1>

<br>
<br>
<br>
<br>


<form action = '/categories/<%=@category.id%>/video_clues/<%=@videos.id%>' >
<button class="btn btn-default btn-lg">Play</button>
</form>

</center>

问题表种子

     Questionnaire.create({question: "In what year did MTV (Music Television)        premiere and what was the first music video the channel aired?", choices:['1982      Michael Jackson Bille Jean', '1984 Madonna Like a virgin', '1981 The Buggles           Video Killed The Radio Star'], correct_answer:"1981 The Buggles Video Killed The      Radio Star", category_id:1})
     Questionnaire.create({question:"This game launched in 1991 on Sega Genesis which the player's mission is to collect as many golden rings as possible", choices:['Battletoads', 'Sonic The Hedgehog', 'Jewel Master'], correct_answer: "Sonic The Hedgehog", category_id:1})
     Questionnaire.create({question: "This sitcom featured four girls living under one roof. They attended the same boarding school, ran a shop together and reside in a town called Peekskill." , choices:['Designing Women', 'The Facts of Life', 'Girlfriends'], correct_answer:'The Facts of Life', category_id: 2})
     Questionnaire.create({question: "This martial arts film premiere in 1985 which featured a young man who studies Bruce Lee's techniques while on the search for his master. This was set in New York City." , choices:['The Last Dragon', 'The Karate Kid', 'Big Trouble in Little China'], correct_answer:'The Last Dragon', category_id: 3})
     Questionnaire.create({question:"This game launched in 1991 on Sega Genesis which the player's mission is to collect as many golden rings as possible", choices:['Battletoads', 'Sonic The Hedgehog', 'Jewel Master'], correct_answer: "Sonic The Hedgehog", category_id:4})

Answer 1

您可以使用窗口函数。

假设您有一个带有类别列的问题表，

select * from (
    select *,
    row_number() over (partition by "category" order by random()) as ordinal
    from questions
) X
 where ordinal = 1
;

这将从问题表中为每个类别随机提供一行(以及一个额外的“序数”始终为 1) 的列。它不一定特别有效。

如果问题变化不大，并且上述查询实际上太慢，您可以保留每个类别的问题总数，并通过使用这些总数来生成特定的问题编号每个类别的随机数，然后将该结果与问题表。但首先要进行基准测试，以上可能已经足够快了。