I'm currently representing friends in a table called relations, which has two columns (userA, userB) which are the user IDs of two users. If user 1 and user 3 are friends, I'd insert (1, 3) and (3, 1) into this table.
I also have a table called posts, which has, among other fields, an id and an ownerId.
So something like this:
relations:
userA | userB
1 | 3
3 | 1
3 | 4
4 | 3
4 | 2
2 | 4
posts:
id | ownerId | ...
1 | 2 | ...
2 | 3 | ...
3 | 4 | ...
I'd like to select every post whose owner is a friend or friend of friend.
For example, if the user the query was selecting for was userId=1, the query should return posts 2 and 3, since userId=3 is a friend, and userId=4 is a friend of a friend).
I've come up with the following, but was wondering if there are better ways to do this (that don't involve a UNION).
SELECT posts.id FROM posts
INNER JOIN relations ON relations.userA=[userId]
WHERE relations.userB=posts.ownerId
UNION
SELECT posts.id FROM posts
INNER JOIN relations AS r1 ON r1.userA=[userId]
INNER JOIN relations AS r2 ON r2.userA=r1.userB
WHERE r2.userB=posts.ownerId AND r2.userB<>[userId];
UNION
? What indexes do you have? Maybe adding a certain composite index is all you need.