Using Models as Join Tables and/or Self Referential Joins?

Rails
#1

Given a User model, I would like to describe a relationship between
two users and add additional information about the connection. For
example, user A and B msg’d each other on X date, and are Y miles
apart. In the book Agile Web Dev with Rails (pg 337), they describe
using models as join tables to add more information than foreign
keys. However, join table models seems to only help describe
relationships if you have two models.

What do you use if you only have one User model and want to add
additional information about relationships between users?

I’ve googled through self referential joins, but most solutions only
outline simple relationship titles. For example, user A is boss of
user B. How would you add more information to this relationship in
one model?

Any advice is welcome, the solution need not be limited to one model.

#2

On 19 Dec 2007, at 08:52, Chris wrote:

additional information about relationships between users?

See http://blog.hasmanythrough.com/2006/4/21/self-referential-through

Fred

#3

On Dec 19, 2007, at 2:52 AM, Chris wrote:

additional information about relationships between users?
Do you really want to limit your message tracking to just one user
per user? In your example, if you’d have to have two fields,
last_messaged_user_id and last_messaged_date on your User model. So
your two user records would look like:

id name last_messaged_user_id last_messaged_date
1 A 2
2007-12-19
2 B 1
2007-12-19

You would never be able to track multiple messages this way. A
better design, if I understand your problem (which I doubt), is to
use a join table and a has_many :through. The join table would have a
from_user_id, a to_user_id, a date/time, and could even have the
message, in which case, you could call it messages. Even if you don’t
keep the message text here, my opinion is to not clutter up the user
record with information not specifically related to the user. This
often results in over-normalization, in which case I sometimes have
to denormalize some things, but I’d rather go that way than to have
chaotic tables/models.

Peace,
Phillip

#4

Fred: Thanks for the link, I came across that article after doing on
search on googletalk about self-referential models. I wasn’t sure it
allowed for more than declaring simple relationships. Over time, I
would like to add more information about the relationship, perhaps I
didn’t understand the article clearly – I will take a look at it
again.

Phillip: I believe you actually do sort of get what I’m trying to do,
and I’m just not thinking correctly about how the database should be
set up. If I understand your post correctly, you recommend I add a
message model and then I can use a join table: message_user. This
would be much better than self-referencing because even if I could
figure out a way to describe a relationship among users within a
single model, that information would clutter up in a single model
solution.

Ryan: I think your solution is a better/simpler way of doing what I
am trying to accomplish. Which leads me to a few other questions…

  1. With a messages and users table I’m doing a 2 model w/ join table
    solution, correct? If this is the case, how would you keep track of
    relationships among users, if messaging doesn’t equal having a
    relationship?

  2. Also, any additional information about the relationship, say,
    distance, level of correspondence, etc. that wouldn’t cleanly fit into
    a messages model. How would you go about organizing any additional
    relationship info.? Make a new model and join table between each User
    model and (X Info) model? (It’s obvious to me now that using only a
    self-referential technique would lead to a massive and painfully slow
    users table)

  3. I’m still on rails 1.2 and learning through the classic agile
    dev. Is it worth upgrading to 2.0 now? I don’t need to be bleeding
    edge, and still learning the basics.

Chris

#5

Wouldn’t you have a messages table (and therefore a messages table)
where
you would keep a from_id and a to_id representing both users, a message
field and a created_at field? Then from there you can get both the from
and
the to user and work out how far apart they are. I’m assuming you’re
using
rails 2.0 here.

class Message
belongs_to :from, :class_name => “User”
belongs_to :to, :class_name => “User”
end

On Dec 20, 2007 12:02 AM, Phillip K. [email protected]
wrote:

keys. However, join table models seems to only help describe
id name last_messaged_user_id last_messaged_date
keep the message text here, my opinion is to not clutter up the user

–
Ryan B.
http://www.frozenplague.net

#6

On Dec 20, 2007 12:01 PM, Chris [email protected] wrote:

Ryan: I think your solution is a better/simpler way of doing what I
am trying to accomplish. Which leads me to a few other questions…

  1. With a messages and users table I’m doing a 2 model w/ join table
    solution, correct? If this is the case, how would you keep track of
    relationships among users, if messaging doesn’t equal having a
    relationship?

You’re doing two tables, users & messages, The messages table has two
fields
that link it to the users table. These are from_id and to_id. I’m
guessing
the last question in that point is that you want to see how many times a
user has messaged another user. Unfortunately there’s no easy way to go
about this. You would do a find on the Message model looking for all
messages that a user has sent to another user. Something like
Message.find_by_from_id_and_to_id(from,to).

  1. Also, any additional information about the relationship, say,
    distance, level of correspondence, etc. that wouldn’t cleanly fit into
    a messages model. How would you go about organizing any additional
    relationship info.? Make a new model and join table between each User
    model and (X Info) model? (It’s obvious to me now that using only a
    self-referential technique would lead to a massive and painfully slow
    users table)

This can be worked out on the fly when needed I guess. It’s not
processor
intensive (is it?).

  1. I’m still on rails 1.2 and learning through the classic agile
    dev. Is it worth upgrading to 2.0 now? I don’t need to be bleeding
    edge, and still learning the basics.

Upgrading is advisable.

Ryan B.
http://www.frozenplague.net

#7

I think I’m sort of seeing what you are trying to do. I would
probably have a users table/model, a messages table/model, and a
relationships table/model:

users have_many relationships
users have_many messages

relationships have_many users
messages have_many users

The relationships table would link the users together, and on that
table you store the information specific to the relationship
(distance, level of correspondence, etc). One user can be part of
many relationships.

The messages table would be an instance of a message between two
users and could have from_user, to_user, sent_at, subject, body,
parent_message_id (for threading/replies).

Peace,
Phillip

#8

Phillip: That sounds great, I have a clear understanding of the next
steps to implement. About the relationship model though, it will have
two foreign keys (one for each user) along with a few other useful
columns. Even though it will always be 2 users to a relationship, we
would still consider it a many-to-many relationship (users table and
relationship table), thus requiring a join table relationships_users
and messages_users?

#9

Phillip: Haha, no you’re assumption is correct. Thanks for all your
help.

Ryan: I’ll take a look at upgrading when I can find a good milestone
to stop at on my current project. Have your heard of any projects in
development breaking after upgrading to the recent 2.0?

#10

On Dec 19, 2007, at 8:50 PM, Chris S. wrote:

Phillip: That sounds great, I have a clear understanding of the next
steps to implement. About the relationship model though, it will have
two foreign keys (one for each user) along with a few other useful
columns. Even though it will always be 2 users to a relationship, we
would still consider it a many-to-many relationship (users table and
relationship table), thus requiring a join table relationships_users
and messages_users?

Well, let me ask you this: Can User A be in only one relationship at
a time? So if User A is in a relationship with User B, User C is out
in the cold until User D comes along? If that’s true, then no, you
don’t need a many to many. But I was going on the assumption that
User A could have simultaneous relationships with any number of other
users, in which case you need a many to many. Unless there is a gap
in my knowledge, which there often is.

Phillip

#11

Nope. Rails 2.0 is designed to be as stable or even more stable than
previous versions.

On Dec 20, 2007 2:37 PM, Chris S. [email protected] wrote:

Phillip: Haha, no you’re assumption is correct. Thanks for all your
help.

Ryan: I’ll take a look at upgrading when I can find a good milestone
to stop at on my current project. Have your heard of any projects in
development breaking after upgrading to the recent 2.0?

–
Ryan B.
http://www.frozenplague.net

#12

Ran into a wall when trying to implement a n-to-n relationship between
two models: User and Relationship. One user can have multiple
relationships with other users. One relationship has two users.

My models:

class Relationship < ActiveRecord::Base
has_many :relationships_users
has_many :users, :through => :users_relationships
end

class User < ActiveRecord::Base
has_many :relationships_users
has_many :relationships, :through => :users_relationships
end

My migration:

class CreateRelationships < ActiveRecord::Migration
def self.up
create_table :relationships do |t|
t.column :person1, :integer
t.column :person2, :integer
t.column :created_at, :datetime
end

create_table :users_relationships, :id => false do |t|
  t.column :user_id, :integer
  t.column :relationship_id, :integer
end

…
end

I am currently able to have users populate the relationships table so
it fills with user_id’s in the person1&2 columns; relationship
established. But I can’t figure out how to utilize the connections
I’ve formed in rails to make calls like:

I have no idea if the code below is the proper way to go about this

user = Relationship.find_all_by_person1(“35”) # This should find all
rows where user_id 35 appears in the person1 column and returns an
array??
relationship_user1 = User.find_by_id(user)
relationship_user1.display_photo

In other words, I know which two users are paired, but how do I pull
information out of the User model through the relationship table? I’ve
spent the whole day reading through all the explanations on the
internet on this topic, so please no links. I’m having great
difficulty understanding the theoretical syntax to traverse through
these relationships.

#13

Ok cool, if I use habtm what will the syntax look like to call a user
method from the relationship table?

#14

The current models does not make much sense. Without the requirements
it is not possible to help

Sent from my iPhone

#15

Blargh!

has_and_belongs_to_many is your friend. Please show him/her/it some
respect.

On Dec 21, 2007 5:14 PM, Chris S. [email protected] wrote:

end
class CreateRelationships < ActiveRecord::Migration
end

difficulty understanding the theoretical syntax to traverse through
these relationships.

–
Ryan B.
http://www.frozenplague.net

#16

your migration don’t look correct then using through you should not
use:
create_table :users_relationships, :id => false do |t|
just use
create_table :relationships_users

regards
svend

#17

bcp: My apologies, for not stating my goals clearly. I am trying to
keep track of relationships among users with a users table and
relationships table. The requirements are that there can only be two
users in one relationship and I should be able to retrieve information
about each user in a relationship given a relationship_id. I am
trying to accomplish this with the models I’ve written.

gundestrup: I think you’re right, I’ve tried implementing habtm and
through relationships at the same time and end up not being able to do
either. Thanks for catching that. I’ve removed the :through in each
of my models and they are now all has_and_belongs_to_many in the User
and Relationship models, which justifies the :id => false, me
thinks. In any case, can you describe what the CRUD syntax will be
if I’m using habtm or through relationships to retrieve data from the
users table?

#18

#1. What is the person1, person2 columns?
#2. What are the concepts in your domain?
#3. What is the use case?

It seems to me your current models are not having the right attributes
or
you are missing a key abstraction in your domain.

#19

Just replied to another mail doing something very similar, and I have no
idea if it’ll work, but you could try:

class User < ActiveRecord::Base
has_and_belongs_to_many :relationships, :foreign_key => “from_id”
has_and_belongs_to_many :related_with, :table_name =>
“relationships_users”,
:foreign_key => “to_id”
end

On Dec 22, 2007 7:17 AM, Bala P. [email protected] wrote:

bcp: My apologies, for not stating my goals clearly. I am trying to

–
Ryan B.
http://www.frozenplague.net

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs