Rails ActiveRecord - Best way to perform an include?

Question

I have three models:

class Book < ActiveRecord::Base
  has_many :collections
  has_many :users, :through => :collections
end

class User < ActiveRecord::Base
  has_many :collections
  has_many :books, :through => :collections
end

class Collection < ActiveRecord::Base
  belongs_to :book
  belongs_to :user
end

I'm trying to display a list of the books and have a link to either add or remove from the user's collection. I can't quite figure out the best syntax to do this.

For example, if I do the following:

Controller

class BooksController < ApplicationController
  def index
    @books = Book.all
  end
end

View

...
<% if book.users.include?(current_user) %>
...

or obviously the inverse...

...
<% if current_user.books.include?(book) %>
...

Then queries are sent for each book to check on that include? which is wasteful. I was thinking of adding the users or collections to the :include on the Book.all, but I'm not sure this is the best way. Effectively all I need is the book object and just a boolean column of whether or not the current user has the book in their collection, but I'm not sure how to forumlate the query in order to do that.

Thanks in advance for your help.

-Damien

Answer 1

Use exists? on the association as it is direct SQL call. The association array is NOT loaded to perform these checks.

books.users.exists?(current_user)

This is the SQL executed by Rails.

SELECT `users`.id FROM `users` 
INNER JOIN `collections` ON `users`.id = `collections`.user_id 
WHERE (`users`.`id` = 2) AND ((`collections`.book_id = 1)) LIMIT 1

In the above SQL current_user id = 2 and book id is 1

current_user.books.exists?(book)

This is the SQL executed by Rails.

SELECT `books`.id FROM `books` 
INNER JOIN `collections` ON `books`.id = `collections`.book_id 
WHERE (`books`.`id` = 3) AND ((`collections`.user_id = 4)) LIMIT 1

In the above SQL current_user id = 4 and book id is 3

For more details, refer to the documentation of the exists? method in a :has_many association.

Edit: I have included additional information to validate my answer.

Answer 2

I would first create an instance method in the User model that 'caches' the all the Book ID's in his collection:

def book_ids
  @book_ids ||= self.books.all(:select => "id").map(&:id)
end

This will only execute the SQL query once per controller request. Then create another instance method on the User model that takes a book_id as a parameter and checks to see if its included in his book collection.

def has_book?(book_id)
  book_ids.include?(book_id)
end

Then while you iterate through the books:

<% if current_user.has_book?(book.id) %>

Only 2 SQL queries for that controller request :)

Answer 3

You're going to to want 2 SQL queries, and O(1) based lookups (probably irrelevant, but it's the principle) to check if they have the book.

The initial calls.

@books = Book.all
@user = User.find(params[:id], :include => :collections)

Next, you're going to want to write the books the user has into a hash for constant time lookup (if people won't ever have many books, just doing an array.include? is fine).

@user_has_books = Hash.new
@user.collections.each{|c|@user_has_books[c.book_id] = true}

And on the display end:

@books.each do |book|
  has_book = @user_has_books.has_key?(book.id)
end

I'd err away from caching the book_ids on the user object, simply because going this route can have some funny and unexpected consequences if you ever start serializing your user objects for whatever reason (i.e. memcached or a queue).

Edit: Loading intermediary collection instead of double loading books.

Answer 4

Essentially you need to make one call to get the book information and the Boolean flag indicating if the current user has the book. ActiveRecord finders doesn't allow you to return the join results from another table. We work around this problem by doing a trick.

In your Book model add this method.

def self.extended_book
  self.columns # load the column definition
  @extended_user ||= self.clone.tap do |klass| 
      klass.columns << (klass.columns_hash["belongs_to_user"] =  
                      ActiveRecord::ConnectionAdapters::Column.new(
                          "belongs_to_user", false, "boolean"))
  end # add a dummy column to the cloned class
end

In your controller use the following code:

@books = Book.extended_book.all(
           :select => "books.*, IF(collections.id, 1, 0) AS belongs_to_user",  
           :joins => "LEFT OUTER JOIN collections 
                        ON book.id = collections.book_id AND 
                           collections.user_id = #{current_user.id}"
          )

Now in your view you can do the following.

book.belongs_to_user?

Explanation:

In the extended_book method you are creating a copy of Book class and adding a dummy column belongs_to_user to the hash. During the query extra join column is not rejected as it exists in the columns_hash. You should use the extended_book only for querying. If you use it for CRUD operations DB will throw error.

Answer 5

I have created a gem(select_extra_columns) for returning join/calculated/aggregate columns in a ActiveRecord finders. Using this gem, you will be able to get the book details and the flag indicating if the current user has the book in one query.

In your User model register the select_extra_columns feature.

class Book < ActiveRecord::Base
  select_extra_columns
  has_many :collections
  has_many :users, :through => :collections
end

Now in your controller add this line:

@books = Book.all(
           :select => "books.*, IF(collections.id, 1, 0) AS belongs_to_user",
           :extra_columns => {:belongs_to_user => :boolean},
           :joins => "LEFT OUTER JOIN collections 
                        ON book.id = collections.book_id AND 
                           collections.user_id = #{current_user.id}"
          )

Now in your view you can do the following.

book.belongs_to_user?