I am doing an ActiveRecord query with a join. I have Surveys that are made up of Questions (where I store the response). I'd like to limit by/select the records where an enum'd value (status) on Question. is "nil", as it hasn't been set by the user.

I can get the join returning what I want, except for limiting by the enum value to "nil" or "".

Here is the Survey model:

class Survey < ApplicationRecord

  enum question_one_response: { very_dissapointed: 0, somewhat_dissapointed: 1, not_dissapointed: 2 }

  belongs_to :product
  has_one :survey_request
  has_many :questions

  accepts_nested_attributes_for :questions
  validates :questions, :presence => true

end

Here is the Question model:

class Question < ApplicationRecord

  enum status: { useful: 0, not_useful: 1, archived: 2 }
  belongs_to :survey

end

status is an integer

Here is the code that "works" but doesn't include the "nil" requirement:

@response = Question.joins(:survey).where('surveys.product_id = ? and surveys.question_one_response = ? and questions.question_number = ?', @product.id, 1, 4)

Here are several variants I have tried without any success:

@response = Question.joins(:survey).where('surveys.product_id = ? and surveys.question_one_response = ? and questions.question_number = ? and questions.status = ?', @product.id, 1, 4, nil)

@response = Question.joins(:survey).where('surveys.product_id = ? and surveys.question_one_response = ? and questions.question_number = ? and questions.status = ?', @product.id, 1, 4, "")

@response = Question.joins(:survey).where('surveys.product_id = ? and surveys.question_one_response = ? and questions.question_number = ? and questions.status = ?', @product.id, 1, 4, blank?)

1 Answers

1
Iceman On Best Solutions

You have to use either

Question
  .joins(:survey)
  .where(surveys: { product_id: @product.id })
  .where(surveys: { question_one_response: 1 })
  .where(question_number: 4)
  .where(status: nil)

or ....

Question
  .joins(:survey)
  .where(surveys: { product_id: @product.id })
  .where(surveys: { question_one_response: 1 })
  .where(question_number: 4)
  .where('status IS NULL')