Rails complex SQL for objects belonging to a user's followers

I am trying to formulate a complex SQL query (in Rails 3) for accessing photo objects belonging to a user's followers.

The scenario would be:

• User A has 500 followers
• Each follower has 500 photos
• I want to generate a query that paginates (using will_paginate) 10 photos at a time based on their creation date and return them to User A

So this would basically be like collecting all of the follower's photos into a set, sorting the set based on the photo creation date, and then paginating the results to return only 10 photos at a time. This could mean that the most recent 10 photos are from multiple followers.

I have two questions about this:

1. How would you efficiently formulate a complex query like this? (I am relatively new to Rails, so I don't understand custom queries that well).
2. Is there a reason not to do it this way? For example, will there be latency issues doing it this way if a user has 1,000 followers who have 10,000 photos each?

The relationships in the User.rb file are modeled as:

has_many :following, :through => :relationships, :source => :followed
h开发者_如何学运维as_many :followers, :through => :reverse_relationships, :source => :follower

has_many :photos,   :dependent => :destroy, :order => 'created_at DESC'

where a follower/following is just another User.

Thank you for any guidance you can provide! It is much appreciated!!! :)

I doubt there would be latency issues since will_paginate puts a limit on the query, in your case 10. However, you would need to make sure indexes exist on the database.

I think something like this should work:

has_many :follower_photos, :class_name => 'Photo', :through => :followers

Is that what you're looking for?