Rails - Jul 2008 - 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 = courses.department_id and
                                 departments.division_id = divisions.id and
                                 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(tm) I''d like to find it,
rather
than having lots of SQL hanging out in my models.

Thanks in advance!
Joe

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---