CHawk CHawk - 2 years ago 154
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.


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


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

The N+1 is happening at
, 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
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 Source

The easiest way is to use group_by:

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

then in the view

<% review = @user_reviews[] %>

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):

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

and then this:

<% if destination.review_foo %>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download