Clever Geek Handbook

Is there a more efficient way to encode this with ActiveRecord?

I have three tables:

  • Users
  • Questions

  • User_Questions

    1.user questions contain columns user_id, question_id and answer

I want to find a random question that hasn't been answered and therefore doesn't have a row in the user_questions table .

If each question has been answered, return any random question.

I'm told it can be done with an OUTER JOIN, but I'm a SQL noob and I'm not sure how to do it in Rails.
This is what I have:

def next_question          
  q = Question.all - Question.joins(:user_questions).where
       (user_questions: { user_id: user_id })
  q = Question.all if q.empty?
  return q[rand(q.size)]
end
+3


source to share


2 answers


There is practically no reason to call a method all

on a model class. This loads every record in a database of that type into memory, and if you're not sure if it's a small set of records, you can hang your whole system. Even then, it's a very bad form to load all over and then cherry picks one thing and discards the rest. It is like ordering one of each item from Amazon, picking the nib you want and throwing the rest of the shipping in the basket.

What you probably want is where you randomly select one entry that hasn't been assigned yet. It probably looks something like this:

Question.where('id NOT IN (SELECT question_id FROM user_questions WHERE user_id=?)', user_id).order('RAND()').first


The problem with this JOIN

is that you will find records that have matches in the table user_questions

, not the opposite.

This query assumes that the number of questions asked by the user is relatively small, or that it NOT IN

could become significantly more expensive.

+1


source


Yes, you can use LEFT OUTER JOIN for this. A regular INNER JOIN will only include rows that match a join condition, a LEFT JOIN will include matching rows and will display unmatched rows by putting NULLs on all columns ( PostgreSQL docs have a reasonable description).

So you do a LEFT JOIN and then look for the unmatched string looking for NULL. SQL would look something like this:

select ...
from questions
left outer join user_questions on questions.id = user_questions.question_id
where user_questions.question_id is null

This will give you all the questions not answered. In ActiveRecord, you can do something like this:

Question.joins('left outer join user_questions on question.id = user_questions.question_id')
        .where(:user_questions => { :question_id => nil })
        .limit(1)


You might want to play with a random order, but that should get you started. If that doesn't give you a match, you might end up with a random question with something like this:

n = Questions.count
q = Questions.offset(rand(n)).limit(1)

You could do the above with help Questions.order('random()').limit(1)

, but it ORDER BY random()

can be frustrating with a large database; fetching an invoice should be pretty fast, so random picking in Ruby should be fast enough that it doesn't make your database hate you.

Also check out the docs for ways to get this off joins

, I'm working with a database with a slightly non-standard structure so I sometimes have to do something long; ActiveRecord refuses to do LEFT JOIN for me unless I have written it, YMMV.

+1


source






More articles:


All Articles