How do I loop in between rows to output a random "something" one at a time using POSTGRES?

Question

I'm building a quiz app and I have a table full of questions that belongs to a category. Each category has their own id number. I can't figure out a way to loop thorough the table that would give me one random question at a time. If someone knows of a possible solution please share. Thanks!

Question 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

Show Page for the questions

<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>

Question Table Seed

     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})

This stackoverflow article [quick random row selection in Postgres](http://stackoverflow.com/questions/5297396/quick-random-row-selection-in-postgres) might help. — Yen-Ju, Jul 13 '15 at 02:03

score 1 · Answer 1 · answered Jul 13 '15 at 02:09

You can use a window function.

Assuming you have a questions table with a category column,

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

This will give one random row per category from the questions table (and an extra "ordinal" column which will always be 1). It's not necessarily particularly efficient.

If the questions don't change that much, and the above query is actually too slow, you could keep a total of questions per category and get a particular question number by getting using those totals to generate a random number for each category and then joining that result with the questions table. But benchmark first, the above is likely to be fast enough.

How do you use it in Rails? Does it go in my Question controller? — Michael Stokes, Jul 13 '15 at 03:57
Can rails make and use an arbitrary query? If not, can it use a view? If it can, you could make a view in the database and then access that. But I'm guessing, I've never used rails. — user464502, Jul 13 '15 at 05:35

How do I loop in between rows to output a random "something" one at a time using POSTGRES?

1 Answers1