STI has_many :through problem

Hi there,

this is my first post here and I'm pretty new to Rails, too. So be
kind, please. :wink:

I'm building a role-based system and I want some of the roles to have
their own model/class. So I decided for a STI scenario where I have
User model, then Salesman and BillingClerk models which are derived
from the first one. Then, when I instantiate the current user after
the authentification I create either an instance of the Salesman or
the BillingClerk class. I'd like to have it like this because I want
to define associations for each of these models separately - because
although salesmen and billing clerks relate to the same entity (eg.
an ad), they relate to it in a different way (salesman has many ads
through ad assignments and billing clerk has many ads through all of
his salesmen). I tried two approaches, but none of them worked as I
wanted:

Here is my first try:

class User < ActiveRecord::Base
  ...
end

class Salesman < User
  has_many :ad_assignments
  has_many :ads, :through => :ad_assignments
  belongs_to :billing_clerk
  ...
end

class BillingClerk < User
  has_many :salesmen
  has_many :ads, :through => :salesmen, :source => :billing_clerk
  ...
end

This approach didn't work at all:

Mysql::Error: Not unique table/alias: 'users': SELECT users.* FROM
users INNER JOIN users ON users.id = users.billing_clerk_id WHERE
(users.deleted_at IS NULL OR users.deleted_at > '2008-04-27 21:11:05')
AND ((users.billing_clerk_id = 4) AND ((users.type = 'Salesman'))) AND
( (users.`type` = 'BillingClerk' ) )

With my second try (although not that clean as the first one) I had
more luck. After I found out that rails doesn't know how to handle
multiple :through associations on the same table (or am I wrong at
this?), I decided to tell it how to handle ads association in the
BillingClerk model, so it now looks like this:

class BillingClerk < User
  has_many :salesmen
  has_many :ads, :class_name => 'Ad',
          :finder_sql => 'SELECT ads.* FROM ads
                          LEFT JOIN ad_assignments ON
ads.ad_assignment_id = ad_assignments.id
                          LEFT JOIN users salesmen ON
ad_assignments.user_id = salesmen.id
                          WHERE salesmen.billing_clerk_id = #{id}'

  ...
end

This approach works. Kind of. I can get an array of Ad objects when i
call BillingClerk.find(:first).ads. That's fine. But when i try to get
some subset of ads through find method, eg.
BillingClerk.find(:first).ads.find :first, I get just nil. How is that
possible? I just don't understand it. Can someone help?

I'm using Rails 1.8.6.

Thank you in advance,
Milan

Milan,

See if this thread helps at all:

http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/9c63361bbd8ea541/3887b4c8ddbf25e2?hl=en&lnk=st&q=Single+table#3887b4c8ddbf25e2

The example that AWDWR gives for STI is pretty nice. I'd recommend
checking it out. (Sorry - not much time today.)

-Kyle