Use :include statement to find records

Question

I'm working on Ruby On Rails 2.3.2 and I'm learning how to use the :include statement.

I'd like to get all the announcements order by their rate. I've got two models and tables for that: Announcement and Rate.

The models look like this:

 class Announcement <
 ActiveRecord::Base

   belongs_to :rate

 end

 class Rate < ActiveRecord::Base

   belongs_to :announcement

end

I'd like to do something like the following, but using :include statement.

Announcement.find_by_sql 'select * from announcements ann inner join rates r on ann.id = r.announcement_id order by r.average DESC'

I already tried this:

Announcement.paginate :page => params[:page], :per_page => 10, :include => [:rates], :order => 'rates.average DESC'

but it's trying to associate rates.id = announcements.id, instead of rates.announcement_id = announcement_id

How can I specify the correct relationship to make this work?

Answer 1

As Yuri points out in the comments, you have incorrectly defined your relationship.

Announcements should have_one :rate

As a named scope on announcment:

class Announcement < ActiveRecord::Base

  has_one :rate
  named_scope :order_by_rate, :include => :rate, :order => 'rates.average DESC'

end

Announcement.order_by_rate.paginate :per_page => 10, :page => params[:page]

Note the difference between :include and :joins as a find option. :joins will join the on the association(s) or SQL JOIN fragment given for purposes of complex WHERE/ORDER clauses. :include will only works on associations, it provides the same advantages of :joins, and also eager load the association.

Where eager loading makes a difference:

With include, @a.rate is populated in the same SQL statement that populates @a.

@a = Association.first :include => :rate

Without include @a.rate is not populated until required.

@a = Association.first
@a.rate #=> @a.rate is populated here, with a separate SQL statement.