I have a People table and a Tasks table. Some People are Managers, some
are Workers. Each Task has a Manager and a Worker.
How do I define the relationships? I tried a STI setup, where Managers
and Workers inherit from People, and People has a type column. In my
Task table, I have worker_id and manager_id rows. Then:
I forgot to mention that you must to define a default scope for getting first all Managers or Workers in each case and a before create filter to set the role the person you are creating will have or whatever:
# in models/user.rb
ROLES = %w[Manager Worker]
def roles=(roles)
self.roles_mask = (roles & ROLES).map { |r| 2**ROLES.index(r) }.sum
end
def roles
ROLES.reject do |r|
((roles_mask || 0) & 2**ROLES.index(r)).zero?
end
end
def is?(role)
roles.include?(role.to_s)
end
Or install the gem that does this [http://rubygems.org/gems/role_model](http://rubygems.org/gems/role_model)
Not exactly the same thing but I think you can relate.
In an application I am working on right now I have 2 separate tables,
one for Roles (manager, worker, etc) and another one for Users
(people). I chose that design instead of keeping a list of roles in
the code because it keeps my application 'cleaner' in the sense that
if a new role is needed a person with enough authority to do so can
just add the new role to the DB through a maintenance page. The User
record has a column for 'role_id' that you can easily maintain also
through the user pages.
oh... and if you do have some criteria that determine who can be
assigned to be a manager or worker, then then you can add a
:conditions element too. So if you have a "people_permitted_to_manage"
table, you can join to that to filter the person_id values that can go
into manager_id in Task.
This works, as far as finding both workers and managers for any given
task. All CRUD look good.
I also need to sort on either the worker name or the manager name, or
both. I can't make this work, since there is no actual field named
"worker.name", for example.
No luck. The query ends with "ORDER BY worker.name, manager.name", and
as these are not columns in the Tasks table, it won't work.
Of course, it is possible to do this in SQL (i.e., sort tasks based on
the names in the People table, with separate sorts for managers and
workers).
SELECT t.job_number, p1.name, p2.name FROM tasks AS t LEFT JOIN people
AS p1 ON t.worker_id = p1.id LEFT JOIN people AS p2 ON t.manager_id =
p2.id ORDER BY p1.name, p2.name
Here, p1.name will be the worker name, and p2.name will be the manager
name.
The question is, should I struggle to generate this through ActiveRecord
constructs, or just call the raw SQL? If there is a reasonable way to do
this via AR, I'd rather do that, but I have no fear of the raw call.
Sorry, my quick answer was hideously wrong to assume the joins that AR
would create would be called "worker" and "manager".
I've just tried this on a table of mine, which has two associations to
a "people" table, one as :person, and one as :owner, and the following
works:
Enrollment.all(:include => [:person, :owner], :order =>
"owners_enrollments.lastname, people.lastname")
Please note that I discovered the table alias depends on the position
in the array of includes (for instance if I had ":include => [:owner,
:person]", the alias was different. But if you determine (I used the
console to troubleshoot) what the alias is, then your query should
work as you want as long as you don't fiddle with it!