Help with ActiveRecord query

Question

Models:

class User < ActiveRecord::Base
  has_many :attendances
  has_many :courses, :through => :attendances
end

class Course < ActiveRecord::Base
  has_many :attendances
end

class Attendance < ActiveRecord::Base
  belongs_to :user
  belongs_to :course
end

Migrations:

create_table(:users) do |t|
  t.string :name
end

create_table(:courses) do |t|
  t.string :name
end

create_table(:attendances) do |t|
  t.references :user, :course
  t.date :date
end

Question

I would like to query:

• a list of all courses
• the date of a given user's last attendance (if any) for each course

What is the best way to tie the following together into a single query?

@courses = Course.all
@user = User.first
@attendances = @user.attendances.group('course_id').order('date DESC')

Note that there is a requirement to include courses that a user has not yet attended.

Any advice much appreciated.

Update

The result I am looking for is as follows:

Course            Last attended
===============================
Some course       2011-03-09
More training     Not Attended
Another course    2010-12-25

In SQL, I would write this query as:

SELECT * FROM courses AS c
LEFT OUTER JOIN attendances AS a ON a.course_id=c.id
WHERE a.user_id=1
GROUP BY a.course_id
ORDER BY a.date DESC;

I could always execute this SQL from within Rails, but I would prefer to avoid this and do things "the Rails way" if possible.

Answer 1

The "Rails-Way" is to define small finders in the model and then to chain them in the controllers.

In class Attendance you could define a new method

def self.last_attendance
  maximum("date")
end

In class Corse ...

def self.ordered
  order("name DESC")
end

And so on. In the controller then, you use them in different combinations. The big advantage of this approach is

• Reusability of finder methods
• Decoupling of classes
• Better readability of code

Answer 2

Your has_many through relation should be like this:

  class User < ActiveRecord::Base
    has_many :attendances
    has_many :courses, :through => :attendances
  end

  class Course < ActiveRecord::Base
    has_many :attendances
    has_many :users, :through => :attendances
  end

  class Attendance < ActiveRecord::Base
    belongs_to :user
    belongs_to :course
  end

And please explain your problem. Because

@user.courses will give you only related courses
Course.all will give you all courses

Edited:

Course.find(:all, :joins => 'LEFT OUTER JOIN attendances ON attendances.course_id = 
courses.id', :conditions => ['attendances.user_id = ?', 1], :group => 
'attendances.course_id', :order => 'attendances.date DESC')

I hope it will work for you.