CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3performance
iwasrobbed
iwasrobbed

Reputation: 46703

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:

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

Upvotes: 1

Views: 150

Answers (1)

twmills
twmills

Reputation: 3015

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?

Upvotes: 1

Related Questions