CHawk CHawk - 7 months ago 46
SQL Question

Rails - Eager load 2 tables, but filter one - Fixing N+1

I have three models:

class User < ActiveRecord::Base
has_many :destinations, :through => :trips
has_many :destination_reviews

class Destination < ActiveRecord::Base
has_many :users, :through => :trips
has_many :destination_reviews

class DestinationReview < ActiveRecord::Base
belongs_to :user
belongs_to :destination


Different users can leave reviews for the same destination.

We're building a table that shows all destinations belonging to a user. The user has left reviews for some (but not all) of those destinations.

Controller:

@user = User.find_by_id(params[:id])
@user_reviews = @user.destination_reviews


View:

<% @user.destinations.each do |destination| %>
<% review = @user_reviews.find_by_destination_id(dest.id) %>
<% if review %>
<div class="review>
...show the review
</div>
<% else %>
<div class="add-review>
...prompt user to add a review
</div>
<% end %>
<% end %>


The N+1 is happening at
@user_reviews.find_by_destination_id(dest.id)
, where @user_reviews is a preloaded list of the user's reviews.

How can I eager load all destinations belonging to this user, including any reviews the user has left for these destinations?

I'm looking for some query like:

dest_ids = current_user.destinations.pluck(:id)`
Destination.includes(:destination_reviews).where(id: dest_ids).where('destination_reviews.user_id = ?', user_id)


But that throws the error:
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "destination_reviews"


Alternatively, if there was a way I could write
@user_reviews.find_by_destination_id(dest.id)
without having it re-query the database, that would work.

Please let me know if that is clear or if I should add more details

Answer

The easiest way is to use group_by:

@user_reviews = @user.desintation_reviews.group_by(&:destination_id)

then in the view

<% review = @user_reviews[dest.id] %>

That gives you one query to get the user's destinations, and another to get their reviews.

Or you could get it down to a single query with some joins plus using select to create some pseudo-attributes on the Destination, and then you can just pull everything you want from dest. It's going to be more complicated though, especially since you're going through a join table. It's going to be something like this (not tested):

current_user.destinations.
             joins("LEFT OUTER JOIN destination_reviews r " +
                   "ON r.user_id = trips.user_id " +
                   "AND r.destination_id = destinations.id").
             select("destinations.*, r.foo AS review_foo, r.bar AS review_bar")

and then this:

<% if destination.review_foo %>
Comments