Awkward SQL query for "friend feed"

Question

I am trying to code a social networking element to our website. We have two tables, one showing users and one showing friendships between those users.

Users:
 userid - PK, int
 name
 profilepic

Friendships:
 id - PK int
 userid
 friendid
 datecreated

The friendship table is two-way - ie someone can befriend someone without them friending them back

I need a query that will fetch all friends for a user, new friendships that have been made by their friends, and the new friendships' names and profilepic's.

A nightmare! All help greatly appreciated!

Answer 1

This is not something that should be done in pure SQL. While your relationships may be trivially defined now, you may (will?) want to add thing to the list of items on the "feed". This something that is much better suited to being aggregated in the consuming code.

Answer 2

try this (sql server):

declare @users table (userid int primary key identity(1,1), name varchar(10), profilepic char(1))
declare @friendships table (id int primary key identity(1,1), userid int, friendid int, datecreated datetime)

insert into @users values ('bill','b')
insert into @users values ('matt','m')
insert into @users values ('steve','s')
insert into @users values ('fred','f')
insert into @users values ('joe','j')

insert into @friendships values (1,2,'8/24/2008')
insert into @friendships values (1,3,'8/24/2008')
insert into @friendships values (2,1,'8/24/2008')
insert into @friendships values (2,3,'8/24/2008')
insert into @friendships values (4,1,'8/24/2008')
insert into @friendships values (4,5,'8/24/2009')

--all firends
select
    u.*
    FROM @users u
        INNER JOIN (select
                        userid
                        FROM @Friendships
                        WHERE friendid=1
                    UNION
                    select
                        friendid
                        FROM @Friendships
                        WHERE userid=1
                   ) dt ON u.userid=dt.userid



--new firends of friends
select
    u.*
    FROM (select
              userid
              FROM @Friendships
              WHERE friendid=1
          UNION
          select
              friendid
              FROM @Friendships
              WHERE userid=1
         ) dt
        INNER JOIN @friendships f ON dt.userid=f.userID
        INNER JOIN @users u ON f.friendid=u.userid
    WHERE f.datecreated>=GETDATE()-14 --2 weeks

OUTPUT

userid      name       profilepic
----------- ---------- ----------
2           matt       m
3           steve      s
4           fred       f

(3 row(s) affected)

userid      name       profilepic
----------- ---------- ----------
5           joe        j

(1 row(s) affected)

Answer 3

-- all friends for a user
select f.*
from Users u
inner join Friendships f
on u.userid = f.userid
where u.userid = 1

-- new friends of friends
select ffu.*
from Users u
inner join Friendships f
on u.userid = f.userid
inner join Friendships ff
on f.friendid = ff.userid
inner join Users ffu
on ff.userid = ffu.userid
where u.userid = 1
and ff.datecreated > getdate() -30

Answer 4

Right, you've got two different queries here, the one for friend info and the one for new-friend-of-friend info. There's no real point trying to do them together.

all friends for a user

SELECT friends.userid, friends.name, friends.profilepic
FROM friendships
JOIN users AS friends ON friends.userid=friendships.friendid
WHERE friendships.userid=[user's id]

new friendships that have been made by their friends

OK, that's a friend-of-a-friend relationship, which you can find by going through the friendship table twice. This is called a self-join:

SELECT friendoffriends.userid, friendoffriends.name, friendoffriends.profilepic
FROM friendships AS ships1
JOIN friendships AS ships2 ON ships2.userid=ships1.friendid
JOIN users AS friendoffriends ON friends.userid=ships2.friendid
WHERE ships1.userid=[user's id]
AND ships2.datecreated>=[last visit time]

Answer 5

This will show your friend's new friends, which new friends are not already your own friends:

SELECT DISTINCT fof.* 
FROM
(
  SELECT u.userid AS id, u.name, u.profilepic
  FROM users u
  JOIN friendships my_friends_friends
    ON my_friends_friends.friendid = u.userid
  JOIN friendships my_friends
    ON my_friends.friendid = my_friends_friends.userid
  WHERE
    my_friends.userid = 1
    AND
    my_friends_friends.datecreated > CURRENT_DATE - INTERVAL '10 DAY'
) fof
LEFT JOIN 
( 
  SELECT friendships.friendid 
  FROM friendships WHERE friendships.userid = 1
) f
  ON f.friendid = fof.id
WHERE
  f.friendid IS NULL
;

Warning: using this query may get you in trouble with the DISTINCT police. :)

Answer 6

The guy with the SQL SERVER syntax has a promissing solution. You can easyly adapt the code for mysql, etc. OK everything seems to be running well but WHAT INDEXING SHOULD WE USE ON BOTH TABLES?