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:
=== My Solutions ===
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 columns: id, name
Friendships table columns: user_id, friend_id
Checkin table 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