Mysql – How to select all online friends

join;MySQL

I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following

Users

+-----+------------+---------------------------------------------------------------+
| uid | timestamp  | profile_img                                                   |
+-----+------------+---------------------------------------------------------------+
|   1 | 1322858590 | /photos/356a192b7b/profile.jpg                                |
|   2 |   13700000 | /photos/356a192b7/profile.jpg                                 |
+-----+------------+---------------------------------------------------------------+

Friends

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
+------+------+

now I want to select all online friends (online = timestamp > current time – 30 seconds)

My try to create a query to select online friends is

SELECT  f.uid1 as friend1,
    f.uid2 as friend2,
    u1.profile_img profile_img1,
    u2.profile_img profile_img2
FROM Friends f
INNER JOIN Users u1 ON u1.uid = f.uid1
INNER JOIN Users u2 ON u2.uid = f.uid2
WHERE 
    f.uid1 = 1 OR 
    f.uid2 = 1;

I have no idea how to compose the timestamp check, considering that

  • friend1 can be me, but it can also be my friend.
  • friend2 can be me, but it can also be my friend.

Is there a solution for this or should I think a bit more about database schema?

Best Answer

Personally, I would have friends be a one-way association; this would double the length of your friends table but eliminate many places where you would need to duplicate logic depending on whether the current user was listed on the left or the right of each entry.

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
|    2 |    1 |
+------+------+

Given a choice between saving RAM/hard drive space, and simpler business logic, the choice should almost always fall to simpler rather than smaller. I suspect that most use-cases will have this performing better as well.

Related Question