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! 
Dave L. wrote:
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!
not being a brain in the group, here is some general information,
to be supplimented by others.
OLD WAY - has_and_belongs_to_many :my_model
This is an out of date technique to set up the many-to-many table. A
major limitation to this method is that you cannot have any more fields
in the table then the 2 joining fields, and the join table does not use
a primary field key called ‘id’. You can’t sort joined fields very
easily with this method.
This technique is very easy to set up. The table name is the plural
names of the 2 joining tables, in alphabetical order, separated by an
underscore. Table C and B would be BS_CS. The only 2 fields in the join
table would be b_id and c_id.
NEW WAY - has_many :through
This technique allows the join table to have as many fields as you
want. You need a model, for the joining, to make this work. You can just
generate a model. But if you need more fields, you can scaffold. The
join table also gets the primary key field (’id’) that is automatically
generated in most cases.
The joining table can be named what ever you want. The current trend
is to use a table name that just has one word, and usually ends in
-ship, -tion, or -ment. There can be endless hours of debate over the
name of the joining table.
You will need to have the 2 joining table’s foreign key fields in
this table, like the fashion ‘_id’, that are not pluralized.
If you wanted to join table A and table B, an example of the joining
table could be:
TABLES
A
id (integer, primary key)
name (string)
B
id (integer, primary key)
name (string)
AB (many table for joining tables A and B)
id (integer, primary key)
a_id (integer)
b_id (integer)
… (more fields)
The MODELS
A
has_many :ABs
has_many :Bs, :through => :ABs
AB
belongs_to :A
belongs_to :B
B
has_many :ABs
has_many :As, :through => :ABs
SOME OPTIONS
has_many :mytable, :foreign_key => 'myfield_id',
:class_name => 'myclassname',
:dependent => :destroyI 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
Can one user admire many other users? Perhaps have another table outside
of
users called admirees_admirers and store the admiree_id and admirer_id
in
there.
Reference via the user model like this:
class User < ActiveRecord::Base
has_and_belongs_to_many :admirers, :join_table => “admirees_admirers”,
:foreign_key => “admiree_id”
has_and_belongs_to_many :admirees, :join_table => “admirees_admirers”,
:foreign_key => “admirer_id”
end
On Dec 22, 2007 5:00 AM, Dave L. [email protected] wrote:
greatly appreciated. Thanks!
–
Ryan B.
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?
@user.admirers SHOULD be enough.
On Dec 22, 2007 8:42 PM, Dave L. [email protected] wrote:
in
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!–
Ryan B.http://www.frozenplague.net
–
Ryan B.
Feel free to add me to MSN and/or GTalk as this email.
On Dec 23, 2007, at 10:36 AM, Dave L. wrote:
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
On Dec 23, 2007, at 5:51 PM, Dave L. wrote:
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. 
Peace,
Phillip
How does this translate to ruby (active record) code? Thanks man,
Dave
Ryan thought his syntax was correct, AGAIN!
I see what it’s doing and Phillip is right about the SQL. I’ll have a
shot
at it during my lunch break (in an hour or two), and I’ll get back to
you.
On Dec 24, 2007 10:42 AM, Phillip K. [email protected]
wrote:
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.Peace,
Phillip
–
Ryan B.
Feel free to add me to MSN and/or GTalk as this email.
Alrighty, didn’t get the time off I expected during my lunchbreak (went
out
for lunch, for around 2 hours :)) so I’ll do it tonight probably.
On Dec 24, 2007 10:48 AM, Ryan B. [email protected] wrote:
little thought, but I’m thinking Ryan will probably come up with
–
Ryan B.http://www.frozenplague.net
Feel free to add me to MSN and/or GTalk as this email.
–
Ryan B.
Feel free to add me to MSN and/or GTalk as this email.
whenvever you get a chance. I appreciate it. Thanks.
On Dec 21, 10:30 am, Dave L. [email protected] wrote:
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.
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
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))
On Dec 25, 4:12 am, “Jeremy McAnally” [email protected]
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
On Dec 22, 2007 4:20 AM, Ryan B. [email protected] wrote:
:foreign_key => “admirer_id”
table. Now all I want to do is go through the admirers table and for
–
http://www.jeremymcanally.com/
My books:
Ruby in Practice
My free Ruby e-book
My blogs:
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
Looks like maybe that was my fault…try this. 
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.
–Jeremy
On Dec 25, 2007 10:03 AM, Dave L. [email protected] wrote:
proper way to do this is something like:
endclass User < ActiveRecord::Base
object. It has a admirer_id and an admired_id. There is also a user
My books:
Ruby in Practicehttp://www.manning.com/mcanally/My free Ruby e-bookhttp://www.humblelittlerubybook.com/
My blogs:http://www.mrneighborly.com/http://www.rubyinpractice.com/
–
http://www.jeremymcanally.com/
My books:
Ruby in Practice
My free Ruby e-book
My blogs:
On Dec 25, 11:53 am, “Jeremy McAnally” [email protected]
wrote:
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
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? 
HABTM is, and has been for some time, considered bad practice compared
to HMT:
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 F. 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
On Dec 25, 2007 12:38 PM, Mark W. [email protected] wrote:
–
http://www.jeremymcanally.com/
My books:
Ruby in Practice
My free Ruby e-book
My blogs: