Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 880
jimgreer Avatar asked Feb 14 '09 17:02

jimgreer


People also ask

What is ActiveRecord in Ruby on Rails?

Active Record is the M in MVC - the model - which is the layer of the system responsible for representing business data and logic. Active Record facilitates the creation and use of business objects whose data requires persistent storage to a database.

Is ActiveRecord an ORM?

ActiveRecord is an ORM. It's a layer of Ruby code that runs between your database and your logic code.

What is ActiveRecord base?

ActiveRecord::Base indicates that the ActiveRecord class or module has a static inner class called Base that you're extending.


2 Answers

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.

like image 192
vladr Avatar answered Oct 13 '22 01:10

vladr


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

like image 29
LDomagala Avatar answered Oct 13 '22 00:10

LDomagala