How can I select all online friends?

Question

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?

Answer 1

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.

Answer 2

Something like this should work. Not sure how well MySQL will take the OR statement though.

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 OR u1.uid = f.uid2
WHERE u1.uid = 1
and u1.Timestamp < dateadd(ss, -30, current_time)

Answer 3

I agree with Myrddin that in the long run one-way associations are probably better. Could use triggers to force them to always in pairs.

Barring that: the OR in your own query is not optimized by MySQL and will make it very slow, let alone after adding any timestamp clauses.

Also: I don't understand the timestamp. Is that the time the user was last logged in? Will you be updating this table continuously? I doubt that would scale. Also, the test should then be: (online = timestamp > current time - 30 seconds) correct?

Anyway, you can get rid of the OR and make the timestamp check easier by using a UNION. Note that I assume the timestamp field is a DATETIME, change it accordingly if it's an epoch serial or whatever.

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 AND (u2.timestamp > NOW() - INTERVAL 30 SECOND) 

UNION ALL

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.uid2 = 1 AND (u1.timestamp > NOW() - INTERVAL 30 SECOND) 
;

How do you actually make sure friends are not linked twice? (1, 10) and (10, 1) for example. Do you have triggers in place? A TRIGGER to always force the lowest id to be the first user for example?