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?
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.
ActiveRecord is an ORM. It's a layer of Ruby code that runs between your database and your logic code.
ActiveRecord::Base indicates that the ActiveRecord class or module has a static inner class called Base that you're extending.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With