Using a join table to exclude records from appearing in a random query

Question

I'm working on a rails app where a user is presented with a random question from a user selected category. If the user has answered the question, I would like for it to never be presented to the user again.

The answer to the question is stored in a memories table. The memories table acts as a join table between the user and the question. In the rails console I can isolate all of the users who have answered a specific question using question = Question.first, and question.users will return an array of user objects who have answered that question.

In rails, I cannot figure out how to exclude answered questions from being presented again.

Guidance?

As a heads up, I'm about 12 weeks into my rails/ruby development. I have a suspicion this is so easy that I just can't see it.

My QuestionsController - this works to present a random question, but presents even if it has been answered by the user previously:

class QuestionsController < ApplicationController

  def index
   @category = Category.find(params[:category_id])
   @question = Question.where(category_id: @category.id).sample
  end

  def show
   @question = Question.find(params[:id])
  end

end

Question Model

class Question < ActiveRecord::Base
  validates_presence_of :category_id

  has_many :memories,
    inverse_of: :question

  has_many :users,
    through: :memories

  belongs_to :category,
    inverse_of: :questions

end

User Model

class User < ActiveRecord::Base
  validates_presence_of :first_name, :last_name, :role

  # Include default devise modules. Others available are:
  # :confirmable, :lockable, :timeoutable and :omniauthable
  devise :database_authenticatable, :registerable,
     :recoverable, :rememberable, :trackable, :validatable

  def is_admin?
    role == 'admin'
  end

  has_many :memories,
    inverse_of: :user,
    dependent: :destroy

  has_many :questions,
    through: :memories

end

Memory Model

class Memory < ActiveRecord::Base
  validates_presence_of :user, :question

  belongs_to :question,
    inverse_of: :memories

  belongs_to :user,
    inverse_of: :memories

  validates :question, uniqueness: { :scope => :user }

end

The validation in my memory model prevents a user from answering a question they have already answered - so that's a step in the right direction. I'd like the question to never show up again once answered.

This is my first post here. Excited to join the community and hope to be able to pay it forward one day. Thank you for any guidance.

Answer 1

You can try

@question = Question.where(category_id: @category.id)
  .where("id NOT IN(select question_id from memories where user_id = ?)", current_user.id)
  .sample

Answer 2

You have two rather complex problems to solve in your question.

First is proper randomization of records, you can find a lot of good answers on SO:

• Rails select random record
• Rails 3: Get Random Record

And the second is a way of selecting data that is NOT associated through a relation aka exclusion of records.

You can do this through a subquery: Rails exclusion query with active record -- Rails 3.1

Or through outer joins: Finding records with no associated records in rails 3

As a side note:

Strive to use ActiveRecords relations all the time, so @category.questions instead of Question.where(category_id: @category.id).

If you implement the randomization and the selections properly via scopes, it should be possible to write concise code like:

@questions = @category.questions.unanswered(current_user.id).random