Trouble on eager loading and using the 'where' method

Question

I am running Ruby on Rails 3.1. I am using the includes method and I would like to understand why when I call the where method on an eager loaded collection it reloads associated objects instead of just finding that object in the eager loaded collection. Since all related objects are already been eager loaded I expect that the where method does not hit the database to reload those objects!

That is, I have the following:

article_categories =
  article
    .categories
    .where(:user_id => @current_user.id)
    .includes(:comments)

If I run

# CASE 1:

article_categories.each do |article_category|
  article_category.comments.map(&:title)
end

eager loading works as expected: the "N + 1 query problem" is avoided. However, the above code returns also comment titles that have not ":user_id == @current_user.id", but I do not want to retrieve those ones at all.

So, since I thought that by using the eager loading I already get all comments, I used a further where(:user_id => @current_user.id) statement like in the following code:

# CASE 2:

article_categories.each do |article_category|
  article_category.comments.where(:user_id => @current_user.id).map(&:title)
end

However, in this case eager loading does not work as expected: the "N + 1 query problem" is not avoided... but comments have ":user_id == @current_user.id"!

I would like to eager loading comments with ":user_id == @current_user.id" (how can I make that by taking advantage from the eager loading?) and I would like to understand why the where statement cancels eager loading effects.

Mischa · Accepted Answer · 2012-03-09T05:39:20.643

3

In case 1 the where clause only applies to categories. If you also want it to apply to comments you can do this:

article_categories =
  article
    .categories
    .includes(:comments)
    .where('categories.user_id = ? AND comments.user_id = ?', @current_user.id, @current_user.id)

edited Mar 09 '12 at 05:39

answered Mar 09 '12 at 04:54

Mischa

42,876
8
99
111

The `where` method "refers to"/"runs on" `categories`, not on `comments`. Both, `categories` and `comments` have the `user_id` attribute/column. – Backo Mar 09 '12 at 05:03
So you need the where clause on both categories and comments?? What do you mean? Your question seems to be about the fact that there are comments that don't have `user_id == @current_user.id`. My answer fixes that. – Mischa Mar 09 '12 at 05:05
I mean that I would like to retrieve all `categories.where(:user_id => @current_user.id)` and eager load all `article.comments.where(:category_id => category_ids, :user_id => @current_user.id)`. `category_ids` is the array of the mentioned `categories.where(:user_id => @current_user.id)`. *P.S.*: I do not know if I was clear. – Backo Mar 09 '12 at 05:12
"there are comments that don't have user_id == @current_user.id". Yes, it is. – Backo Mar 09 '12 at 05:16
I opened a more specific question at http://stackoverflow.com/questions/9629860/trouble-on-eager-loading-second-degree-associated-objects. – Backo Mar 09 '12 at 06:30

Trouble on eager loading and using the 'where' method

1 Answers1

Linked