Why does the where() method run SQL queries after all nested relations are eager-loaded?

Question

In my controller method for the the index view I have the following line.

@students_instance = Student.includes(:memo_tests => {:memo_target => :memo_level})

So for each Student I eager-load all necessary info.

Later on in a .map block, I call the .where() method on one of the relations as shown below.

@all_students = @students_instance.map do |student|

...

last_pass = student.memo_tests.where(:result => true).last.created_at.utc
difference_in_weeks = ((last_pass.to_i - current_date.to_i) / 1.week).round

...

end

This leads to a single SQL query for each student. And since I have over 300+ students, leads to very slow load times and over 300+ SQL queries.

Am I right in thinking that this is caused by the .where() method. I think this because I have checked everything else and these are the two lines that cause all of the queries.

More importantly, is there a better way to do this that reduces these queries to a single query?

Answer 1

The moment you ask where, the statement is translated to a query. Normally, the result should be sql-cached...

Anyway, in order to be sure, you can instead add programming logic to your statement. That way, you are not requesting a NEW sql statement.

last_pass = student.memo_tests.map {|m| m.created_at if m.result}.compact.sort.last

EDIT I see the OP's question does not require sorting... So, leaving the sorting out:

last_pass = student.memo_tests.map {|m| m.created_at if m.result}.compact.last

compact is required to remove nil results from the array.