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];