Struggling with a complex Rails 3 query (need: user.friends.checkins)

Question

I'm working on a social networking application and am trying to build a complex query that efficiently pulls all of a users' friends' checkins from the database. Basically I need: "user.friends.checkins"

I've recreated (in simplified form) the data structure below for reference and included several solutions I've found, however I feel like my solutions are sub-optimal. I'm hoping that someone can point out a better way... So here goes:

I tried this first, which works, but is way too slow given that users typically have +1000 friends each:

=> Checkin.where(:user_id => self.friends)

Then I tried this, which also works and is much faster, but feels sloppy:

=> Checkin.joins(:user).joins('INNER JOIN "friendships" ON
"users"."id" = "friendships"."friend_id"').where(:friendships =>
{:user_id => 1})

Any help you can provide would be GREATLY appreciated! Thanks in advance!!!

=== Data structure ===

Users table has columns: id, name
Friendships table has columns: user_id, friend_id
Checkins table has columns: id, location, user_id

=== Models ===

class User
 has_many :friendships
 has_many :friends, :through => :friendships
 has_many :checkins
end

class Friendship
 belongs_to :user
 belongs_to :friend, :class_name => 'User'
end

class Checkin
 belongs_to :user
end

Answer 1

The first query should serve you well. Add indexes to the foreign key fields in the query.

Answer 2

Best solution I've found so far:

Checkin.joins(:user => :friendships).where('friendships.friend_id' => self.id)