CODEWITHSUNDEEP
sqlruby-on-railsormrails-activerecord
ahnbizcad
ahnbizcad

Reputation: 10797

How to eager load associated table with conditions and current user?

Im trying to reduce the number of queries from n+1 to a couple for hacks that have favorites.

User has many hacks. (the creator of the hack) Hack has many favorites User has many favorites Favorites belongs to hack and user.

Favorites is just a join table. It only contains foreign keys, nothing else.

On the hacks index page, I display a star icon based on whether a favorite record exists for the given hack and current_user.

index.hmtl

- @hacks.each do |h| 
  - if h.favorites(current_user)
    #star image

How do I use active record or raw SQL in a '.SQL' method call to eager load the relevant favorites?

I'm thinking first get the paginated hacks, get the current user, and query all of the favorites that have the hack and the current user.

I'm not sure on the syntax. Also Current_user cannot be called from models.

Update:

I tried these queries

@hacks = Hack.includes(:tags, :users, :favorites).where("favorites.user_id = ?", current_user.id).references(:favorites)

I've also tried the following, and both of them without the .references method

@hacks = Hack.includes(:tags, :users, :favorites).where(favorites: { user_id: current_user.id } ).references(:favorites)

The WHERE clause acts on hacks to limit the type of hacks. But what I want is to limit the favorites to the ones with the condition instead (i.e. favorites.user_id = current_user.id).

(After all, many thousands of user may favorite a hack, but the client side is only concerned with the current user, so loading all the users that favorited the hack could be very expensive and bad)

How can I make conditions apply to eager loaded associations rather than the original model?

Upvotes: 1

Views: 424

Answers (1)

vee
vee

Reputation: 38645

If you have belongs_to :user and belongs_to :hack, would it be safe to assume that you have a "has many through" relationship setup between favorites, hack and user? Following is how you could eager load Hack with Favorites and Users. 

# app/controllers/hacks_controller.b

def index
  @hacks = Hack.includes(favorites: :user)
end

The above code is going to run three queries, one to select all from hacks, second join between hacks with favorites, and third select all from users with users already selected in the second query through favorites.

Another way to execute these joins in a single query would be to use an inner join using joins: 

# app/controllers/hacks_controller.b

def index
  @hacks = Hack.joins(favorites: :user)
end

With this query, there is only one query with inner join between the three tables, users, favorites and hacks.

Upvotes: 1

Related Questions