Complex Active Record Associations

Hi, I have a weird situation that I’m hoping to find a better way to solve. I have a situation where I need to only allow users to have certain data based on a division. I think I’m using finder_sql way too much though and there has to be a better way.

Here’s the setup:

User may be an admin, in which case they belong to ALL divisions

class User < ActiveRecord::Base

has_and_belongs_to_many :divisions
end

class Division < ActiveRecord::Base
has_and_belongs_to_many :users

has_many :departments

end

class Department
belongs_to :division
has_many :courses
end

class Course < ActiveRecord::Base

belongs_to :department
end

I want to be able to make sure that a user is only ever to edit/view/delete courses that are within divisions that they are members of. I ended up with this, but it smells bad to me, and I’m hoping there is a better way:

User may be an admin, in which case they belong to ALL divisions

class User < ActiveRecord::Base

has_and_belongs_to_many :divisions

has_many :courses,

      :finder_sql => 'select courses.*
                        from courses,

                             divisions_users,
                             roles_users,

                             divisions,
                             departments

                       where [departments.id](http://departments.id) = courses.department_id and
                             departments.division_id = [divisions.id](http://divisions.id) and

                             [divisions.id](http://divisions.id) = divisions_users.division_id and
                             divisions_users.user_id = roles_users.user_id and

                             if(roles_users.role_id = 3,1,divisions_users.user_id = #{id})'

end

The if(roles_users.role_id = 3,1,divisions_users.user_id = #{id} determines if the user is an admin and takes appropriate measures.

This works fine as I can say:

Select a course that the user has valid access to

user.courses.find(params[:id])

Return a list of all courses the user should see

user.courses

One of the problems with this method is I am stuck with basic find by id’s. If I add anything more complicated (i.e. find :all, :conditions) the scheme breaks. Also,relying on hard coded SQL will make the app a little more brittle. Am I barking up the wrong tree? This is a pattern that recurs through our app so if there is a better way™ I’d like to find it, rather than having lots of SQL hanging out in my models.

Thanks in advance!
Joe