Is it possible to make ActiveRecord create objects for rows loaded using the :joins option?

Question

I need to do something like this

class User < ActiveRecord::Base
  has_many :abuse_reports
end

class AbuseReport < ActiveRecord::Base
  belongs_to :abuser, :class_name => 'User', :foreign_key => 'abuser_id'
  belongs_to :game
end

class Game < ActiveRecord::Base
  has_many :abuse_reports
end

@top_abusers = User.page(params[:page], 
  :joins => [
    'JOIN abuse_reports ON users.id = abuse_reports.abuser_id', 
    'JOIN games ON games.id = abuse_reports.game_id'
  ], 
  :group => 'users.id',
  :select => 'users.*, count(distinct games.id) AS game_count, count(abuse_reports.id) as abuse_report_count',
  :order => 'game_count DESC, abuse_report_count DESC'
)

This works, but doesn't create objects for AbuseReports or Games - it just returns a pile of rows. When I reference these objects from my view it loads them again. Is there a way to fix this? Or some way to get this behavior without using :joins?

Answer 1

The problem you are having is that you use ActiveRecord in way its not "supposed" to be used. By that I mean that you are writing your own sql, which makes AR give up all of its control to you.

If you want AR to handle everything you should try to use it with less of your own SQL in there. It looks like you want to know which user has the highest amount of AbuseReports. Try something like this:

some_user.abuse_reports.count

to get the count of abuse_reports

Answer 2

Firstly, you should really use :include instead of :joins

User.find(:all, :include => { :abuse_reports => [ :game ] }, :order => )

or, in your case, try

User.page(params[:page], :include => { :abuse_reports => [ :game ] })

This will perform the join for you and retrieve the records in one shot.

Now, this may retrieve a given game record for you multiple times (if the same game is tied to a user by multiple reports.) If your game record is large, you can reduce the amount of data exchanged between your app and the RDBMS as follows:

class User < ActiveRecord::Base
  has_many :abuse_reports
  has_many :abused_games, :through => :abuse_reports
end
...

User.find(:all, :include => [ :abuse_reports, :abused_games ])

Finally, you also want to retrieve the counts and sort accordingly. Check out http://railscasts.com/episodes/23 for how to add counter caches into the actual active records (counter caches simplify the SQL and make the RDBMS' life easier and your queries run faster). After you set up the counter caches, you can finally alter the above to do:

User.find(:all, :include => [ :abuse_reports, :abused_games ], :order => 'users.abused_games_count DESC, users.abuse_reports_count DESC')

This will ultimately retrieve your ActiveRecords in one single, simple SQL statement.