简而言之,标题就是问题所在。以下是我正在使用的简化模型:
class Test
include Mongoid::Document
field :name, :type => String
embeds_many :questions
attr_accessible :name, :questions
end
class Question
include Mongoid::Document
field :text, :type => String
embedded_in :test
has_many :answers
attr_accessible :text, :test, answers
end
class Answer
include Mongoid::Document
field :value, :type => Integer
belongs_to :question
belongs_to :user
attr_accessible :value, :question, :user
end
class User
include Mongoid::Document
has_many :answers
attr_accessible :answers
end
我希望能够以最少的数据库查询量为用户检索测试中的所有未回答问题。
这是我迄今为止想到的最好的解决方案
answered = []
user.answers.each {|a| answered << a.question_id}
test.questions.where(:_id.nin => answered).length
非常感谢对此的任何帮助。
更新:您需要查询是否存在受约束的选择:
test.questions.find_by_sql("WHERE NOT EXISTS (SELECT id FROM answers WHERE answers.question_id = questions.id AND answers.user_id = #{user.id}")
或条件连接的计数 0
test.questions.joins("INNER JOIN answers ON answers.question_id = questions.id AND answers.user_id = #{user.id} AS user_answers").where('COUNT(user_answers.id) = 0')
让我知道这些是否有效。 对他们的表现有什么见解吗?
原始答案,不正确,因为它假设我们需要用户提出的完全未回答的问题:
对答案使用计数器缓存。这假定问题数据库表上有一个answers_count整数列。
class Answer
include Mongoid::Document
field :value, :type => Integer
belongs_to :question, :counter_cache => true
belongs_to :user
attr_accessible :value, :question, :user
end
鉴于此,您的查询可以是:
test.questions.where(answers_count: 0, user_id: user)