please someone help with this active record question! its driving me crazy

Im having trouble developing the database structure for the following
scenario. I have a table that only contains 2 fields for each
object. It has a admirer_id and an admired_id. There is also a user
table. Now all I want to do is go through the admirers table and for
each admired_id, take the corresponding admirer_id and collect all the
users that have a corresponding user_id to this admirer_id. Im having
trouble setting up the database relationships and how I should go
about this in general. I think this may involve using a self-
referential has_and_belongs_to_many relationship? Any help/advice is
greatly appreciated. Thanks! :wink:

I would disagree that habtm is out of date. The limitation of not
being able to add columns to the join table is only a limitation if
that's what you need want to do. Otherwise, I think habtm is TSTTCPW.

///ark

So using this setup of another admirees_admirers table, how would you
gather the admirer user objects? would it just be @admirers =
user.admirers.find(:all)? Im unclear how, once you find the admiree,
you then find the corresponding admirers.

Ok thanks. Im making some progress, but I get this error:
Mysql::Error: Not unique table/alias: 'admirers': SELECT * FROM
admirers INNER JOIN admirers ON admirers.id = admirers.admirer_id
WHERE (admirers.admiree_id = 4 )
any idea what this means?

When you reference the same table in a query multiple times, you must alias each:

select *
from admirers a1
inner join admirers a2
on a2.id = a1.someotherid

Peace,
Phillip

How does this translate to ruby (active record) code? Thanks man,
Dave

Ah, well, the down side to participating in this group by email is I don't always have a proper context for answering a question. I thought you were talking about a SQL query. Sorry. I'll give it a little thought, but I'm thinking Ryan will probably come up with something before I will. Assuming he comes out of that Javascript dream, that is. :slight_smile:

Peace,
Phillip

whenvever you get a chance. I appreciate it. Thanks. :slight_smile:

Try this:

class User < ActiveRecord::Base
  has_and_belongs_to_many :admirers,
    :class_name => 'User',
    :join_table => 'admirees_admirers',
    :foreign_key => 'admiree_id',
    :association_foreign_key => 'admirer_id'

  has_and_belongs_to_many :admirees,
    :class_name => 'User',
    :join_table => 'admirees_admirers',
    :foreign_key => 'admirer_id',
    :association_foreign_key => 'admiree_id'
end

///ark

HABTM is pretty much deprecated compared to has_many :through. The
proper way to do this is something like:

class User < ActiveRecord::Base
  has_many :admirations
  has_many :admirers, :through => :admirations
  has_many :admirees, :through => :admirations
end

class Admiration < ActiveRecord::Base
  belongs_to :admirer, :class_name => "User", :foreign_key => "admirer_id"
  belongs_to :admiree, :class_name => "User", :foreign_key => "admiree_id"
end

I think that should work (too late to plug it in and test it)...

The advantage of HM:T is that you can add extra data to the
relationship. For example, you could put "Admired since XX-XX-XXXX"
and plugin the created_at attribute on the Admiration, or you could
add in a field like "knows_admiree" and put something like "Knows and
admires (x)."

--Jeremy

Thanks, it looks like Im getting closer, but I am still getting an
error:
Mysql::Error: Unknown column 'admirations.user_id' in 'where clause':
SELECT users.* FROM users INNER JOIN admirations ON users.id =
admirations.admirer_id WHERE ((admirations.user_id = 1))

Looks like maybe that was my fault...try this. :slight_smile:

class User < ActiveRecord::Base
has_many :admirations, :foreign_key => 'admirer_id', :class_name =>
'Admiration'
has_many :admirees, :through => :admirations

# Bad association name but you can name it whatever
has_many :admireds, :foreign_key => 'admiree_id', :class_name => 'Admiration'
has_many :admirers, :through => :admireds
end

class Admiration < ActiveRecord::Base
belongs_to :admirer, :class_name => "User"
belongs_to :admiree, :class_name => "User"
end

I haven't tried this either but now that my brain is more awake, I
think this should work. :slight_smile:

--Jeremy

Jeremy, habtm is only "deprecated" if you need additional information
in the join table. If you don't, then setting up a model for the join
is unnecessary.

Dave, the code I posted works with habtm. It's the canonical way to do
self-referential many-to-many relationships.

///ark

No, it is not the "canonical" way. Maybe if you like to use out of
date practices, and, if so, perhaps you'd like a little dynamic
scaffolding to go with that HABTM? :wink:

HABTM is, and has been for some time, considered bad practice compared to HMT:

http://www.slideshare.net/rabble/introduction-to-active-record-at-mysql-conference-2007/38
http://onrails.org/articles/2006/11/18/the-rails-edge-conference-in-denver-day-3
(Trying to find more information on this presentation...I had a
link...)
http://blog.hasmanythrough.com/2006/4/2/rich-associations-out-join-models-in

The Rails Way by Obie Fernandez says this:

"Use of habtm, which was one of the original innovative features in
Rails, fell out of favor once the ability to create real join models
was introduced via the has_many :through association."

Of course, you can still use it and it's still valid, but I wouldn't
say it's "canonical" or even good practice.

--Jeremy

No, it is not the "canonical" way.

I used that term since it's the method that's described in Chad
Fowler's "Rails Recipes." Now, some may say that Fowler's book is out
of date, but habtm certainly does seem like the simplest way to
accomplish this task.

HABTM is, and has been for some time, considered bad practice compared to HMT:

Yes, I've heard what people say. All I get out of it is that modelling
join tables allows you to add information to the join. I don't
understand why that would be important if you don't need to do that
(and YAGNI). It's not agile.

Of course, you can still use it and it's still valid, but I wouldn't
say it's "canonical" or even good practice.

It works, and it's the simplest way to solve a problem like the OP's.

///ark

Perfect! This works, you are the man. Thank you. I can now collect
all the user_id's of the admirers of a user by using @user.admireds.
Now if I can only trouble you for some more brain power, I am trying
to use these user_id's to create an instance of @avatars. There is a
user_id field in each avatar object and I want to gather all the
avatar objects in @avatars that correspond to to the @user.admireds.
I think this may be simple, but it is still giving me trouble. Any
thoughts?? Thanks!

Hmm. Again, not trying this in a console or anything, but off the top
of my head, you could try:

@avatars = @user.admireds.map {|user| user.avatar}

Not sure that would work, but you should be able to use some of the
Enumerable methods like that to make it happen.

--Jeremy

HABTM does have its use. It is NOT deprecated and will NEVER be removed out of Rails, period. If your domain does not need any
attributes other than the foreign keys of the tables that are connected
then HABTM is good enough. In this case the association class is just a
join table.

When your application demands that other attributes needs to be
persisted that belongs to the association class then you have to go for
the has_many :through relationship.

I also had a weird case with a many-to-many relationship between two
classes in the same inheritance hierarchy. I had to use hmt for that.

///ark

I never said it was going to be removed from Rails. I do think it's a
gimped solution compared to HMT and is generally viewed as, shall we
say, outmoded in 99% of cases.

--Jeremy