What am I doing wrong with this query?

Question

Goal: to get a list of N random questions that have been answered correctly the least amount of times, per user.

following sql will provide me a list of having a count of correctly answered questions for user_id 4.

select q.id, temp.Count
from questions as q
left join
(select q.id, count(a.id) as count
from questions as q
left join attempts as a
on a.question_id = q.id
where a.correct = true and a.user_id = 4
group by q.id)
temp on temp.id = q.id
order by count desc

Now, in rails, Ive got:

c = Question.find :all, 
:joins => "LEFT JOIN attempts ON attempts.question_id = questions.id", 
:select => "questions.id, count(attempts.id)", 
:group => "questions.id", 
:conditions => ["attempts.correct = true AND attempts.user_id = ?", @user.id]

But this will give me a list of only 1 record, instead of the 43 records I used to get in psql console.

Output:

c = Question.find :all, :joins => "LEFT JOIN attempts ON attempts.question_id = questions.id", :select => "questions.id, count(attempts.id)", :group => "questions.id", :conditions => "attempts.correct = true AND attempts.user_id = 4"
=> [#<Question id: 112>]

Output without :conditions:

b
=> [{"id"=>"69", "count"=>nil}, {"id"=>"70", "count"=>nil}, {"id"=>"71", "count"=>nil}, {"id"=>"72", "count"=>nil}, {"id"=>"73", "count"=>nil}, {"id"=>"74", "count"=>nil}, {"id"=>"75", "count"=>nil}, {"id"=>"76", "count"=>nil}, {"id"=>"77", "count"=>nil}, {"id"=>"78", "count"=>nil}, {"id"=>"80", "count"=>nil}, {"id"=>"81", "count"=>nil}, {"id"=>"82", "count"=>nil}, {"id"=>"83", "count"=>nil}, {"id"=>"84", "count"=>nil}, {"id"=>"85", "count"=>nil}, {"id"=>"86", "count"=>nil}, {"id"=>"87", "count"=>nil}, {"id"=>"88", "count"=>nil}, {"id"=>"89", "count"=>nil}, {"id"=>"90", "count"=>nil}, {"id"=>"91", "count"=>nil}, {"id"=>"92", "count"=>nil}, {"id"=>"93", "count"=>nil}, {"id"=>"94", "count"=>nil}, {"id"=>"95", "count"=>nil}, {"id"=>"96", "count"=>nil}, {"id"=>"97", "count"=>nil}, {"id"=>"98", "count"=>nil}, {"id"=>"99", "count"=>nil}, {"id"=>"100", "count"=>nil}, {"id"=>"101", "count"=>nil}, {"id"=>"102", "count"=>nil}, {"id"=>"103", "count"=>nil}, {"id"=>"104", "count"=>nil}, {"id"=>"105", "count"=>nil}, {"id"=>"106", "count"=>nil}, {"id"=>"107", "count"=>nil}, {"id"=>"108", "count"=>nil}, {"id"=>"109", "count"=>nil}, {"id"=>"110", "count"=>nil}, {"id"=>"111", "count"=>nil}, {"id"=>"112", "count"=>"2"}]

Maybe important: most records end up with NULL for 'count'. I can't seem to set a 0 for them if they haven't been answered yet.

But, as far as the rails problem: I guess this is because of the :conditions. What am I doing wrong? Thanks in advance!

Answer 1

If you're calling the find method on the Question model, then the group by questions.id might be getting applied in the query to the questions table instead of in the inner query as you write above (and hence having no effect).

What's the sql that's getting generated by your rails code? You should be able to see this in your rails log.