CODEWITHSUNDEEP
sqlruby-on-railserd
user1916823
user1916823

Reputation: 143

How to represent interchangeable columns

I'm not quite sure how to phrase this, but is there a good way to implement a table where the columns are essentially interchangeable?

Example: you have a Users model and want to allow two Users to be 'friends'. The obvious way, to me, would be to have a table containing two columns ('friend1' and 'friend2') that each containing the key to a User. This makes it awkward for saying something like "are user1 and user2 friends" because you have to check for "(friend1=user1 AND friend2=user2) OR (friend1=user2 AND friend2=user1)". It would work, but it just seems awkward to me that every time you want to get something from that table you're looking in both columns. Is there a more elegant way do this?

Upvotes: 6

Views: 693

Answers (3)

MorphicPro
MorphicPro

Reputation: 2902

You can do a has_many through or has_and_belongs_to_many http://guides.rubyonrails.org/association_basics.html

any how you want a join table that links your user models.

for example

class User < ActiveRecord::Base
  has_many :followings
  has_many :followers, :through => :followings, :class_name => "User"
  has_many :followees, :through => :followings, :class_name => "User"
end


class Following < ActiveRecord::Base 
  # fields: follower_id followee_id (person being followed)
  belongs_to :follower, :class_name => "User"
  belongs_to :followee, :class_name => "User"
end

same as user has many :users, or must I use another way for a friend based social network?

Upvotes: 0

Patrick
Patrick

Reputation: 136

A key choice when making a friendship relationship, is deciding if it is bi-directional. Twitter following being an example of one directional friendship and Facebook friendships being bi-directional. Sounds like you're committed to the bi-directional, so the 2 options you have are:

1) Check both directions

select *
from friendships 
where (friend1 = 123 and friend2 = 456) OR (friend2 = 123 and friend1 = 456)

2) Always put the lower user_id into friend1 and the higher user_id into friend2, then your test only needs to check one direction. This is a little trickier to maintain, so I'd only do it needed for perf reasons.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269603

The way that you can implement this might seem a bit awkward. The idea is to have a "friendshipId" in a table with two columns: friendshipId and user. Now the users are interchangeable.

To find out if user1 and user2 are friends:

select friendshipId
from friends
group by friendshipId
having sum(case when name = user1 then 1 else 0 end) > 0 and
       sum(case when name = user2 then 1 else 0 end) > 0

Judicious use of constraints, triggers, and stored procedures will ensure that a friend relationship has only two users, that someone cannot friend themselves, and so on.

Upvotes: 1

Related Questions